Countif among filtered data

Hi,

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

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?

Thanks!
LVL 13
Shanan212Asked:
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
    Next
End If
MsgBox i
End Sub

Open in new window

0
 
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


or

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

Open in new window

0
 
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.
0
Upgrade your Question Security!

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

 
NBVCCommented:
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:

=SUMPRODUCT(SUBTOTAL(3,OFFSET($A$1:$A$100,ROW($A$1:$A$100)-MIN(ROW($A$1:$A$100)),,1)),--($B$1:$B$100="Johnny"))

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

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

=SUBTOTAL(#,Range)

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.

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

Thanks
Rob
0
 
NBVCCommented:
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.
0
 
Rob HensonFinance AnalystCommented:
@NB VC
Hence my second comment!
0
 
Shanan212Author Commented:
Thanks!
0
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.