We help IT Professionals succeed at work.

# Excel 2010 - use COUNTIF and COUNTIFS with filters

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

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?

Comment
Watch Question

## View Solution Only

Photographer
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

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

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!