Countifs if one range OR another meets criteria


I am trying to create a countifs formula that counts cells if one range meets criterion OR another range meets a different criterion. I have attached a screen shot of an example. In this example, I want to count the projects with a color of "Red," and either a category of "Difficult" OR a status of "complete" I should get a count of three since projects Alpha, Charlie and Echo have what I'm looking for. Right now, I am only able to create a formula that counts the projects if all three ranges meet all three criteria. Here is my starting point:

=COUNTIFS(Color1:Color6, "Red", [Project Category]1:[Project Category]6, "Difficult", Status1:Status6, "complete")

This formula is excluding project charlie because the category is "Easy" and it's excluding project Echo because the status is "pending."



  • David Joyeuse
    David Joyeuse ✭✭✭✭✭
    edited 07/20/20

    Try this:

    =COUNTIFS(Color1:Color6,"Red",OR([Project Category]1:[Project Category]6="Difficult",Status1:Status6="complete")

    COUNTIFS works as an "AND" function, so you need to integrate the "OR" condition within it :)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!