• Status: Solved
• Priority: Medium
• Security: Public
• Views: 908

# Applying a COUNTIF formula only to visible cells in a filtered list.

Hello Experts,

I need help counting filtered cells that meet a second criteria.  See attached sample worksheet.  I want to change the formula in cell E193 to work when I filter column D.  (E193 should be 18 when filtered for SPARKS.)

Is that doable?

Gary
Training-Records---EE.xls
0
garyrobbins
• 6
• 2
2 Solutions

Commented:
Use the SUBTOTAL function to count only the visible rows:

=SUBTOTAL(2,A2:A1009)           equivalent to COUNT, only just for visible rows
=SUBTOTAL(3,A2:A1009)           equivalent to COUNTA, only just for visible rows

0

Commented:
Here's the formula:

=SUMPRODUCT((\$E\$5:\$E\$188>\$A\$2)*(SUBTOTAL(3;OFFSET(\$E\$5:\$E\$188;ROW(\$E\$5:\$E\$188)-MIN(ROW(\$E\$5:\$E\$188));;1))))

jppinto
Training-Records---EE.xls
0

Commented:
I'd suggest a small tweak to jppinto's suggestion. As it stands the "NA" entries will also count as > A2 (because any text value is deemed to be greater than any numbers)...use SUBTOTAL with first argument of 2 (rather than 3) to count only numbers, i.e.

=SUMPRODUCT(SUBTOTAL(2,OFFSET(E5,ROW(E5:E188)-ROW(E5),0))*(E5:E188>\$A\$2))

regards, barry
0

Commented:
Good point Barry! Thanks for your input on this one...
0

Commented:
I've just changed my SUBTOTAL argument to 2 on my formula and attached the file. Barry's formula is also working.

jppinto
Training-Records---EE.xls
0

Commented:
Gary, allow me to disagree on your points assignment! AS barryhoudini mentioned, he made a "small tweak to" to my formula and you gave all the points to him?! I don't think it's fair...
0

Commented:
0

Author Commented:
jppinto:

I am always looking for the solution that is simplist and one that I can intuitively follow.  In the future if you can offer a brief description of how the formula works I would find that helpful.

I very much appreciate your input and your fast response.  In the future I will consider splitting my point assignment.

Gary
0

Author Commented: