# Countifs with AND/OR

edited 12/09/19

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!

Tags:

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

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.

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

• Thanks Paul! This was helpful!

• ✭✭✭✭✭✭

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!