Countifs if one range OR another meets criteria

Tricia Pekarna

Hi,

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."

Capture.JPG

David Joyeuse

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