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

Excel 2010 - use COUNTIF and COUNTIFS with filters

I have an Excel 2010 document that looks something like this:

Name              Color             Size              Units
Bob Smith         Red               Large                  5
Tim Jones         Blue              Small                  8
Fred Wallace      Red               Small                  1
Ann Smith         Blue              Large                  8

Open in new window

Then, in the cells below my data, I have a formula that counts the number of people whose Units are less than 6:


This returns 2, as it should. The problem is that when I apply filters to the data, this value doesn't change. For example, if I filter for Column D = "Blue", I want the formula to return 0. If I filter for Column C = "Large", I want the formula to return 1.

I also have the same problem with this COUNTIFS function:


Is there a formula I can use that will perform the same function as COUNTIF / COUNTIFS, but will work with the filters?

Thanks for your help.
  • 2
  • 2
1 Solution
Brian PiercePhotographerCommented:
COUNT and COUNTIF take no notice of filters - instead use SUBTOTAL(2,D2:D5)

(2 = Count)
barry houdiniCommented:
.....but SUBTOTAL will count all the numeric values that are visible, the requirement here is to only count visible numbers within a specific range.

For that try this version for counting < 6 excluding filtered out values


and for >=6 and < 10


regards, barry
barry houdiniCommented:
...another way which simplifies the formulas required is to use a helper column, e.g. in column E (or any blank column), use this formula in E2 copied down


you'll get a 1 on visible rows and a zero on the non-visible ones, then you can just use that column in the COUNTIFS formula, e.g.




regards, barry
LCMSdevAuthor Commented:

That's absolutely perfect, thank you very much!

Had to go through the logic to figure out why this worked. Gotta say, it strikes as a bit...doofy. (Nothing against you, of course! I'd just have thought there'd be a formula that did this cleanly.) This sort of thing is why I personally prefer VBA to formulas, but I can't be telling clients to run macros on their reports. I greatly appreciate your help!

-- LMCSdev
LCMSdevAuthor Commented:
Just noticed your last post; that's a very clever hack with any number of applications. I'll definitely file that one away for future use. Thanks again!
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

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