Excel VBA: Formula to Count Specific Values in AutoFiltered Column

Topic

New Reply

Hi Excel VBA Genii,

I know how to count the number of non-blank cells in an AutoFiltered list using a formula like:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(F26,ROW(F26:F247)-ROW(F26),0)),(F26:F247″”)+0)

but I’m darned if I’ve been able to come up with a formula to count the number of instances of a specific value within the AutoFiltered column.

My AutoFiltered column looks like this:

I want to be able to count the number of instances of “Trine, 240°” or “Opposition, 180°” etc.

I’ve tried:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(F26,ROW(F26:F247)-ROW(F26),0)),(F26:F247=”Trine, 240°”)+0)

but that hasn’t worked.

Any bright ideas on how to do this?

Thanks in anticipation.

Cheers

Trevor

Reply | Quote

Replies

Okay Genii, I got it working.

Not sure why but the formula shown above is now working just fine.

Cheers

trevor

Reply | Quote

Hi Trevor

Instead of this long formula..
=SUMPRODUCT(SUBTOTAL(3,OFFSET(F26,ROW(F26:F247)-ROW(F26),0)),(F26:F247=”Trine, 240°”)+0)

You could try my shorter function..
=countIfVisible(F26:F247,”Trine, 240°”)

Or, if your “Trine, 240°” was in say, cell [F17], then the formula would be..
=countIfVisible(F26:F247,F17)

Here’s the function (add it to a regular vba module)

Code:

Function countIfVisible(range, criteria)
For Each cell In range
If Not cell.EntireRow.Hidden Then If cell = criteria Then Count = Count + 1
Next cell
countIfVisible = Count
End Function

zeddy

Reply | Quote

Hi Zeddy,

Many thanks.

That’s an absolutely brilliant solution. So much simpler that the long and complex SUMPRODUCT solution I was using.

Cheer

Trevor

Reply | Quote