Countifs with AND/OR

chadedwardconway

Hi There

I have a Countifs that is counting the number of projects that are healthy (Green), marked as "prioritized", are "in progress" and match a particular category. This works great, except I'm trying to expand this to look for "in progress" projects OR "not started" projects, but maintain all the other AND criteria..

I can't quite figure out how to fit the OR statement into my formula--Would love some recommendations.

This is the formula that I have written:

=COUNTIFS({Master Project List - Project Health}, "Green", {Master Project List - Prioritization}, "Prioritized", {Master Project List - Project Status}, "In Progress", {Master Project List - Category}, Category@row)

 

Thank you for any tips!

Paul Newcome

You would simply use the OR in conjunction with @cell references to turn each criteria into a logical statement to fit with the OR syntax, and use that as the criteria for the specified range.

 

............................{Master Project List - Project Status}, OR(@cell = "In Progress", @cell = "Not Started"), ...............................

Paul Newcome

Note:

 

This can also be done with the AND statement if you catch yourself repeatedly referencing the same range within a formula. I typically see this with date ranges.

 

Instead of 

 

=COUNTIFS({Range 1}, <= DATE(yyyy, mm, dd), {Range 1}, >= DATE(yyyy, mm, dd))

 

you could have

 

=COUNTIFS({Range 1}, AND(@cell <= DATE(yyyy, mm, dd), @cell >= DATE(yyyy, mm, dd)))

.

It may not seem like much in the above example, but when you have a lot of different ranges covering a multitude of criteria, every little bit of consolidation and organization helps.

chadedwardconway

Thanks Paul! This was helpful!

Paul Newcome

Happy to help!