Count IF formula that meets multiple criteria.

I am looking to get an accurate count of someone’s workload. I want to count if a specific person’s workload meets multiple stage criterion such as “Execution” and “Planning”

The Support Required column is a contact list. The Stage column in a dropdown list.

I am pulling this information into a separate sheet hence the {} for the cross sheet formula.

The below formula works and counts all projects they are listed on under “Support Required”

=COUNTIF({Master Sheet Support Required}, FIND("Denise", @cell) > 0)

When I try to add additional criteria it comes back at #incorrect agreement.

=COUNTIFS({Master Sheet Support Required}, FIND("Denise", @cell) > 0, AND(IF({Master Sheet Stage} = "Execution")))


Tags:

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    @AlexisJ COUNTIFS will only count a row if all the criteria are present. So if you want to count multiple possible values in the same range, you need to incorporate an OR function to make that work.

    =COUNTIFS({Master Sheet Support Required}, FIND("Denise", @cell) > 0, {Master Sheet Stage}, OR(@cell = "Execution", @cell = "Planning"))

    You can keep adding more Stage values in the same way within the OR if needed.

    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

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    @AlexisJ Just a syntax issue. In COUNTIFS and SUMIFS, since you can list multiple criteria ranges, the AND is implied and built into the function, plus you don't need the IF in there. The syntax is just COUNTIFS(range1, criteria1, range2, criteria2...) Try this:

    =COUNTIFS({Master Sheet Support Required}, FIND("Denise", @cell) > 0, {Master Sheet Stage}, "Execution")

    If the above has an issue, maybe try:

    =COUNTIFS({Master Sheet Support Required}, FIND("Denise", @cell) > 0, {Master Sheet Stage}, @cell = "Execution")

    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!

  • AlexisJ
    AlexisJ ✭✭✭

    @Jeff Reisman That is helpful! I am now running into another issue. I hope you can help out.

    It works if I am only looking to count 1 stage "Execution". When I try to add a second stage it doesn't count any of the projects and reports a 0.

    =COUNTIFS({Master Sheet Support Required}, FIND("Denise", @cell) > 0, {Master Sheet Stage}, @cell = "Execution", {Master Sheet Stage}, @cell = "Planning")


  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    @AlexisJ COUNTIFS will only count a row if all the criteria are present. So if you want to count multiple possible values in the same range, you need to incorporate an OR function to make that work.

    =COUNTIFS({Master Sheet Support Required}, FIND("Denise", @cell) > 0, {Master Sheet Stage}, OR(@cell = "Execution", @cell = "Planning"))

    You can keep adding more Stage values in the same way within the OR if needed.

    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!

  • AlexisJ
    AlexisJ ✭✭✭

    @Jeff Reisman Thank worked! Thank you so much.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!