asked on

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

jppinto

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

ASKER CERTIFIED SOLUTION

jppinto

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

barry houdini

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jppinto

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

jppinto

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

jppinto

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...

jppinto

Still waiting for your feedback...

garyrobbins

ASKER

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

garyrobbins

ASKER

Please change award to 250 points each. Both had merit.

Gary