Countif among filtered data


I have a filtered set of data. I want to use

Open in new window

on a column to find the number of occurances of a specific data. (how many times a name appears within a filtered month)

However, it is giving me an error.

How can I go on to do this?

LVL 13
Who is Participating?
byundtConnect With a Mentor Commented:
You might consider looping through the visible cells in your range and testing them for the value:
Sub FilteredCellCounter()
Dim cel As Range, rg As Range
Dim i As Long
Set rg = Selection
If rg.Cells.Count = 1 Then Set rg = Intersect(rg.EntireColumn, rg.Worksheet.UsedRange)
Set rg = rg.SpecialCells(xlCellTypeVisible)
If Not rg Is Nothing Then
    For Each cel In rg.Cells
        If cel.Value = "Johnny" Then i = i + 1
End If
MsgBox i
End Sub

Open in new window

NBVCConnect With a Mentor Commented:
Have you tried like this... assigning to a variable

Var = Application.CountIf(Range("A1:A100"), "Johnny")

Open in new window


Msgbox Application.CountIf(Range("A1:A100"), "Johnny")

Open in new window

Shanan212Author Commented:
Hi NB,

Yes I did. It returns an error for filtered rows. That is (eg) when only A15, A22, A99 is visible (even though Jonny is on A22)

I only want it to return results from visible cells only.
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

I am more of a formula person... so not sure what best way would be with VBA, but a formula to count a filtered list based on a condition would be something like:


counts number of "Johnny" in column B when filtering column A.

Not sure if you want to adapt that to VBA and application functions...
Rob HensonConnect With a Mentor Finance AnalystCommented:
Slightly less complicated than SUMPRODUCT, use:


The # can be changed to several options from 1 to 11 and 101 to 111.

The help file gives the meaning of each but 2 or 3 and 102 or 103 use the COUNT and COUNTA functions.

Interpreting the help remarks:

1 to 11 include rows hidden by formatting but excludes rows hidden by filter.
101 to 111 excludes rows hidden by formatting and rows hidden by filter.

Rob H
Rob HensonFinance AnalystCommented:
That assumes that the column is already filtered to the value that you are counting.

My assumption was that Shanon212 wanted to count only a certain name (e.g. only the number of times "Johnny" appears)  after filtering.  The SUBTOTAL() function on its own will count all rows after filtering.
Rob HensonFinance AnalystCommented:
Hence my second comment!
Shanan212Author Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.