We help IT Professionals succeed at work.

Excel 2010 - use COUNTIF and COUNTIFS with filters

Medium Priority
1,297 Views
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:

=COUNTIF(D2:D5,"<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:

=COUNTIFS(D2:D5,">=6",D2:D5,"<10")

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.
Comment
Watch Question

Brian PiercePhotographer
CERTIFIED EXPERT
Awarded 2007
Top Expert 2008

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

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

=SUMPRODUCT(SUBTOTAL(2,OFFSET(D2,ROW(D2:D5)-ROW(D2),0))*(D2:D5<6))

and for >=6 and < 10

=SUMPRODUCT(SUBTOTAL(2,OFFSET(D2,ROW(D2:D5)-ROW(D2),0))*(D2:D5>=6)*(D2:D5<10))

regards, barry
CERTIFIED EXPERT
Most Valuable Expert 2013

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

=SUBTOTAL(2,D2)

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.

=COUNTIFS(D2:D5,"<6",E2:E5,1)

and

=COUNTIFS(D2:D5,">=6",D2:D5,"<10",E2:E5,1)

regards, barry

Author

Commented:
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

Author

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!

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