Help with COUNTIFS with multiple criteria in same range, where to put the OR operator

ShaRobinson

This is a frequent topic in the smartsheet community, and I think I've read most of the responses, but I can't resolve it for my particular situation. : (

Mostly I get unparseable when I add my OR criterion.

This is used on a project portfolio metrics sheet, where I want to count for each particular calendar year how many projects are in either of two statuses ("completed" or "shipped"). Each project has its own row, and among other data elements, the year it is supposed to complete, and it's current status.

Originally, I only wanted to count "completed" - and this formula has worked great:

=COUNTIFS([YEAR]:[YEAR], [list of years]@row, [ProjectStatus]:[ProjectStatus],"Completed")

The YEAR column range is the 4 digit year that each project did or is supposed to complete; the first criterion is [list of years]@row, which is a static reference year column (like 2018 in one row, sequentially increasing at each row; and the Project Status range is self-explanatory.

So now I want to count whether the project status is either "completed" OR "shipped" — but no matter where I've tried to carefully add my "OR" operator, I'm getting unparseable most of the time.

I've tried a bunch of different ways, but here's a few examples that aren't working:

This one errors with Invalid Data:

=COUNTIFS([DC YEAR]:[DC YEAR], [list of years]@row, OR([Project Status]:[Project Status], "Completed", [Project Status]:[Project Status], "Shipped"))

I've also tried adding the OR operator before the first criteria of [list of years], and tried it after [Project Status]:[Project Status], OR("Completed", "Shipped).

I've also tried using "=" instead of comma-separated criteria in my solutions, but that didn't help.. and my original formula works great, so…

ex: [Project Status]:[Project Status] = "Shipped"

Thanks all!!

Sharon

Kelly Moore

Hey @ShaRobinson

Try this

=COUNTIFS([YEAR]:[YEAR], [list of years]@row, [ProjectStatus]:[ProjectStatus],OR(@cell="Completed", @cell="Shipped"))

Does this work for you?
Kelly

Kelly Moore

Hey @ShaRobinson

Try this

=COUNTIFS([YEAR]:[YEAR], [list of years]@row, [ProjectStatus]:[ProjectStatus],OR(@cell="Completed", @cell="Shipped"))

Does this work for you?
Kelly

ShaRobinson

Hi Kelly, that worked perfectly! (I knew to leave spaces before and after "=" operator and such, and after commas.. so it worked on the first try.)

So in case anybody has a similar query as mine, I used Kelly's solution like this:

=COUNTIFS([DC YEAR]:[DC YEAR], [list of years]@row, [Project Status]:[Project Status], OR(@cell = "Completed", @cell = "Shipped"))

Thanks again!!