Combining multiple criteria in COUNTIFS formula
There seems to be an error in my formula. The first part works by itself (counting the number of times the SME's name appears). Then, when I add the critieria to limit the count to those whose status is "Red", I get an "incorrect argument" error. It is selecting the correct data sets to pull from. Please look at my formula and tell me if you can identify a missing comma, parenthesis, or operator. The formula is:
=COUNTIFS(SMEA:SMEC, = "Jane Doe", Status:Status, = "Red")
Comments
-
I wonder if its because your first range has multiple columns and your second range doesn't match. Ttry setting it to =COUNTIFS(SMEA:SMEA, ="Jane Doe", Status:Status, = "Red")
Does it work then?
-
It does BUT I only receive 1/3 the hits I should because the name could appear in multiple columns. Is there a way to make it repeat the count for each column and then add them together?
-
Hi Christie,
You'd have to use the FIND function for it to work.
I'll get back to the post later if you've not solved it before.
Have a fantastic day!
Best,
Andrée Starå
Workflow Consultant @ Get Done Consulting
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
I'd appreciate that. I thought perhaps I could repeat the formula three times and then have it add up but, since I've been using smartsheets a total of one day, I can't figure out how to do nested formulas and no examples seem to be available. There's a seminar but I need something I can refer back to.
Thanks!
-
Andree or Mike will likely have a better formula, but to answer you other question, you can stack them like this:
=COUNTIFS(SMEA:SMEA, "Jane Doe", Status:Status, "Red") + COUNTIFS(SMEB:SMEB, "Jane Doe", Status:Status, "Red") + COUNTIFS(SMEC:SMEC, "Jane Doe", Status:Status, "Red")
-
Good job Nick. I was overcomplicating the matter trying to use @cell references embedded in an OR statement. Haha. That would be the right formula to use.
-
Genius! It works!
-
Try something like this.
=COUNTIFS(SMEA:SMEA; FIND("Jane Doe"; @cell) > 0; Status:Status; "Red") + COUNTIFS(SMEB:SMEB; FIND("Jane Doe"; @cell) > 0; Status:Status; "Red") + COUNTIFS(SMEC:SMEC; FIND("Jane Doe"; @cell) > 0; Status:Status; "Red")
The same version but with the below changes for your and others convenience.
=COUNTIFS(SMEA:SMEA, FIND("Jane Doe", @cell) > 0, Status:Status, "Red") + COUNTIFS(SMEB:SMEB, FIND("Jane Doe", @cell) > 0, Status:Status, "Red") + COUNTIFS(SMEC:SMEC, FIND("Jane Doe", @cell) > 0, Status:Status, "Red")
Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.
Did it work?
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Another option would be to create an additional column (SMEall for example) where you join the SME columns and then reference that in a single COUNTIFS.
.
SMEall would hold a basic
=JOIN(SMEA@row:SMEC@row, ",")
.
Then use
=COUNTIFS(SMEall:SMEall, FIND("Jane Doe", @cell) > 0, Status:Status, "Red")
.
Just another option if you run into a similar problem in the future but have more than just a few columns to add together.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!