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

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
Asked:
garyrobbins
  • 6
  • 2
2 Solutions
 
jppintoCommented:
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
 
jppintoCommented:
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
 
barry houdiniCommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
jppintoCommented:
Good point Barry! Thanks for your input on this one...
0
 
jppintoCommented:
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
 
jppintoCommented:
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
 
jppintoCommented:
Still waiting for your feedback...
0
 
garyrobbinsAuthor 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
 
garyrobbinsAuthor Commented:
Please change award to 250 points each.  Both had merit.

Gary
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now