Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel 2010 - use COUNTIF and COUNTIFS with filters

Posted on 2012-08-14
5
Medium Priority
?
1,218 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.
0
Comment
Question by:LCMSdev
  • 2
  • 2
5 Comments
 
LVL 70

Expert Comment

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

(2 = Count)
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 2000 total points
ID: 38293511
.....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
 
LVL 50

Expert Comment

by:barry houdini
ID: 38293540
...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
 

Author Closing Comment

by:LCMSdev
ID: 38293659
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
 

Author Comment

by:LCMSdev
ID: 38293676
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

With its various features, Office 365 can not only help you with your day-to-day business tasks, it can also do wonders for your marketing campaign.
Quickbooks hosting can do wonders to your enterprise but considering the points elaborated in the article which will help you to better analyze the outcomes. So scan your business, its needs and then move to the new world of limitless benefits.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question