Count of rows excluding a set of values in the another sheet

[sindhuja]

Hello,

Get the counts of autofiltered values except specific values in a column C of sheet 3. For example sheet 2 with autofilter true in column 5. I need to get the count from column A (sheet2) except the values in the column C of Sheet3.

Am struck with comparing the column A value in sheet 2 with the column C of Sheet 3. If they are equal then the count should not be incremented.

-Sindhuja

[Bob Phillips]

Can you show an example, I am a tad confused.

[sindhuja]

Hi Xld,

Have certain criteria based upon which I need to get the counts excluding the Column C values in Sheet3.

For ex, filter “Retail” in column C of Sheet 1. Then in the column A check for the values and get the count excluding set of values in column C of sheet 3.
On the whole total count is 37 on filtering “Retail” out of which 3 should be excluded and the remaining value should be in the respective column (column F of sheet 3) of sheet 3 (37-3=34)

Attached is the sample spreadsheet for your reference.

-Sindhuja

[sindhuja]

Any Clue on my request....

[sindhuja]

Any help on this pls..

[sindhuja]

Can anyone please look into my requirement… its really a concern for me..
Am breaking my heads…

-Sindhuja

[Aussiebear]

I'm assuming that you require something which will count the required value of a filtered list( in this case Column C of Sheet 2), then subtract the number of occurrences in Column A of Sheet 2, any of the values contained within the list in Column C of Sheet 3. Am I right so far?

The counting of a value in a filtered list is simply =CountIf($C$2:$C$83,C2) to be entered in cell C84.

It is after here that maybe XLD will come in with a possible Sumproduct formula to count the number of occurences of any of the nominated values found in Column A.

[mdmackillop]

[VBA]Sub Macro1()
With Sheets("Sheet2")
.Range("$A$1:$I$83").AutoFilter Field:=3, Criteria1:="Retail"
For Each cel In .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp)).SpecialCells(xlCellTypeVisible)
Set c = Sheets("Sheet3").Columns(3).Find(cel)
If c Is Nothing Then
i = i + 1
Else
msg = msg & cel & vbCr
End If
Next
End With
MsgBox msg
MsgBox i
ActiveSheet.Range("$A$1:$I$83").AutoFilter
End Sub
[/VBA]

[Aussiebear]

MD, what happens here if the criteria for the autofilter changes?

[mdmackillop]

[VBA]Criteria1:=InputBox("Filter by")[/VBA]
I was going to leave this for the OP to figure out!

[sindhuja]

Thanks a lot..it worked out for me..

[sindhuja]

Hi,
One more query…

If there is no items to dislay when autofilter on certain criteria it showing me error.

Invalid procedure call or argument in the below line

cel = Left(cel, Len(cel) - 8)

If there are no items to display it means the value is zero
Is there a way to do this..??

[vba]With Sheets("Sheet2")
.Range("$A$1:$l$1000").AutoFilter Field:=8, Criteria1:="Retail"
For Each cel In .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp)).SpecialCells(xlCellTypeVisible)
cel = Left(cel, Len(cel) - 8)

'MsgBox cel
Set c = Sheets("Sheet3").Columns(7).Find(cel)



If c Is Nothing Then
j = j + 1

msg = msg & cel & vbCr
End If
Next
[/vba]

-Sindhuja

[Bob Phillips]

Set up a variable abnd test it

[vba]

With Sheets("Sheet2")

.Range("$A$1:$l$1000").AutoFilter Field:=8, Criteria1:="Retail"
On Error Resume Next
Set rng = .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp)).SpecialCells(xlCellTypeVisible)
On Error Resume Next
If Not rng Is Nothing Then

For Each cel In rng

cel = Left(cel, Len(cel) - 8)

'MsgBox cel
Set c = Sheets("Sheet3").Columns(7).Find(cel)
[/vba]



If c Is Nothing Then
j = j + 1

msg = msg & cel & vbCr
End If
Next