Count Ifs for multiple criteria

Kimberlee Adam · 04/28/22

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)

Jeff Reisman · 04/28/22

@Kimberlee Adam

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.

Jeff Reisman · 04/28/22

@Kimberlee Adam

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.

Kimberlee Adam · 04/28/22

@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. :)

Jeff Reisman · 04/28/22

https://community.smartsheet.com/discussion/comment/327724#Comment_327724

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!

Kimberlee Adam · 04/28/22

@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

Jeff Reisman · 04/28/22

https://community.smartsheet.com/discussion/comment/327743#Comment_327743

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.

Kimberlee Adam · 04/28/22

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!

Jeff Reisman · 04/28/22

https://community.smartsheet.com/discussion/comment/327749#Comment_327749

Excellent, glad you've got it going now.

Count Ifs for multiple criteria

Best Answer

Answers

Help Article Resources

Categories