Using CountIf on filtered range

savindrasingh

Board Regular
Joined
Sep 10, 2009
Messages
183
I need VBA code to findout how many times a word "ACTIVE" has occured in given range. But the issue is, there is one filter applied on the next column and I want to count the occurance of given word in visible rows only. It should not count the words from non-visible rows.

Please assist with the VBA code for this.

Thanks in advance. (I will be leaving form the office, I will revert to the comments by tomorrow morning.)
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Have a look at the SUBTOTAL function. If you use the function_num 103 it will ignore hidden values.

Dom
 
Upvote 0
Its good to know about the use of subtotal function. But can you please help me with the code to achive below req:

1) Count the number of visible cells in given range which contain value/word "Active"
2) I need to store this value in a variable so that I can use it somewhere else.

So please provide me with a VBA code instead of worksheet function.
 
Upvote 0
Does this help at all?

=SUMPRODUCT(SUBTOTAL(3, OFFSET(your_range_with_actives,ROW(your_range_with_actives)-MIN(ROW(your_range_with_actives)),0,1)),--(your_range_with_actives="active"))

Lee
 
Upvote 0
This counts the number of cells in column B with Active in them taking into account a filter:

Code:
Sub test()
Dim lastRow As Long, myRange As Range, myCount As Long
lastRow = Cells(Rows.Count, 2).End(xlUp).Row
For Each myRange In Range("B2:B" & lastRow).SpecialCells(xlCellTypeVisible)
If myRange = "Active" Then myCount = myCount + 1
Next myRange
MsgBox myCount
End Sub

Dom
 
Upvote 0
Thanks Lee,

I have converted that formula to VBA using below line:

Code:
Range("B3").Value="=SUMPRODUCT(SUBTOTAL(3, OFFSET(Sheet1!D:D,ROW(Sheet1!D:D)-MIN(ROW(Sheet1!D:D)),0,1)),--(Sheet1!D:D=""Active""))"
 
Upvote 0
This counts the number of cells in column B with Active in them taking into account a filter:

Code:
Sub test()
Dim lastRow As Long, myRange As Range, myCount As Long
lastRow = Cells(Rows.Count, 2).End(xlUp).Row
For Each myRange In Range("B2:B" & lastRow).SpecialCells(xlCellTypeVisible)
If myRange = "Active" Then myCount = myCount + 1
Next myRange
MsgBox myCount
End Sub

Dom

Thanks Dom ! This is also very useful.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top