We help IT Professionals succeed at work.

Excel 2010 - use COUNTIF and COUNTIFS with filters

Medium Priority
Last Modified: 2012-08-14
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.
Watch Question

Brian PiercePhotographer
Awarded 2007
Top Expert 2008

COUNT and COUNTIF take no notice of filters - instead use SUBTOTAL(2,D2:D5)

(2 = Count)
Most Valuable Expert 2013
.....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
Most Valuable Expert 2013

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



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


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!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.