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

=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.
0
LCMSdev
Asked:
LCMSdev
  • 2
  • 2
1 Solution
 
Brian PiercePhotographerCommented:
COUNT and COUNTIF take no notice of filters - instead use SUBTOTAL(2,D2:D5)

(2 = Count)
0
 
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

=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
0
 
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

=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
0
 
LCMSdevAuthor 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
0
 
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!
0
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