Count Ifs for multiple criteria
I want to count how many developers we have in a certain area based on two criteria, what team they are on and if they are onboarded.
=COUNTIFS({US Digital Team Members Range 2} = "API", [{US Digital Team Members Range 1} = "Building Services & Analytics", {US Digital Team Members Range 3} = "Onboarded"], 0)
Best Answer
-
The syntax for COUNTIFS is COUNTIFS( range1, criterion1, range2, criterion2, ... ). Your ranges to evaluate and the criteria are comma-separated, and you only need math operators (=, >, <, etc) with numeric values. Try this instead:
=COUNTIFS({US Digital Team Members Range 2}, "API", {US Digital Team Members Range 1}, "Building Services & Analytics", {US Digital Team Members Range 3}, "Onboarded")
There are two links in my signature to pages that very helpful with Functions and Formula Error Messages; I use them every day. Bookmark them for future reference.
Regards,
Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers
-
The syntax for COUNTIFS is COUNTIFS( range1, criterion1, range2, criterion2, ... ). Your ranges to evaluate and the criteria are comma-separated, and you only need math operators (=, >, <, etc) with numeric values. Try this instead:
=COUNTIFS({US Digital Team Members Range 2}, "API", {US Digital Team Members Range 1}, "Building Services & Analytics", {US Digital Team Members Range 3}, "Onboarded")
There are two links in my signature to pages that very helpful with Functions and Formula Error Messages; I use them every day. Bookmark them for future reference.
Regards,
Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
@Jeff Reisman Thank you Jeff! This gets me closer but I'm returning a value of 0. And thank you for the links as well, I tried to reference them but for some reason I'm not connecting the dots very well. :)
-
Can you post a screenshot of the data you're trying to count (blur out any sensitive info)?
What are the column formats of the columns you reference in your ranges? If there are multi-select cells that have more than one value in them, we'll have to make your formula a little more complex!
Regards,
Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
@Jeff Reisman , sure! I was also debating if I needed to use checkboxes in the raw data sheet to count but that would be a lot more columns
-
I don't see any rows that meet all three of your criteria. There's no Team row that equals "Building Services & Analytics", so your COUNTIFS will result in 0.
Change that criteria to "Shared Services" and see if you get a count, and if so, then you know your formula works.
Regards,
Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
There is a Team named that, just was not included in my example. When I changed it to Shared Services, it worked. And now I feel like a bonehead because I just realized, I was pulling it from the wrong data source LOL.
Ugh, thank you so much! This works!
-
Excellent, glad you've got it going now.
Regards,
Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Help Article Resources
Categories
Check out the Formula Handbook template!
