I can't figure out how to use the OR function in a Countifs formula

Hello,

I have been creating metrics on my data in smartsheet using the different formulas. I have figured many out (with a lot of head banging and sleepless nights), but cannot figure out how to use the OR function.

works: =COUNTIFS({Week}, =$[Date Range]$3, {Month}, $[Date Range]$5, {Year}, $[Date Range]$8, {task status}, "PCC Case Submitted")

Does not work: =COUNTIFS({Week}, =$[Date Range]$3, {Month}, $[Date Range]$5, {Year}, $[Date Range]$8, OR({task status}, "PCC Case Submitted", "PCC Case Now Closed"))

What I am doing wrong with the or functionality?

Find more posts tagged with

Healthcare

Education

Formulas

IT & Operations

Project Management

Comments

Mike Wilday

You would want to use @cell = for each of your criteria.

Try something like this:

=COUNTIFS({Week}, =$[Date Range]$3, {Month}, $[Date Range]$5, {Year}, $[Date Range]$8, {task status} OR(@cell = "PCC Case Submitted", @cell = "PCC Case Now Closed"))

Mike Wilday

I've attached a screenshot showing a simplified version of the formula.

Paul Newcome

Mike,

Forgot a comma in the posted formula after {task Status}.

=COUNTIFS({Week}, =$[Date Range]$3, {Month}, $[Date Range]$5, {Year}, $[Date Range]$8, {task status}, OR(@cell = "PCC Case Submitted", @cell = "PCC Case Now Closed"))

Colleen,

Another part of the problem with your formula is that you are including your range within the OR function. You should only include the criteria (and Mike's correction of using @cell).

Original:

OR({task status}, "PCC Case Submitted", "PCC Case Now Closed")

Corrected:

{task status}, OR(@cell = "PCC Case Submitted", @cell = "PCC Case Now Closed")

Mike Wilday

Thanks, Paul! Good catch.

Paul Newcome

Sign in to join the conversation!

Quick Links

Trending discussions

INDEX/MATCH Most Recent Entry
I am trying to pull from a sheet that is essentially a list of new records that are entered weekly. To keep things clean I have a helper sheet that just lists the project name once and the rest of the columns pull in through an INDEX/MATCH. I am trying to have that helper sheet pull in only the most recent entry. There is…
Break Up Multi-Entry Row
I'm using a form entry (into an Intake Sheet) in which someone can select multiple locations for the same Initiative. I'm looking to copy the row to a different Master sheet, but would like to have the same row copied multiple times, once per location selected. Intake Sheet: Desired Result in Master Sheet: Basically, I…
Set contents of two columns into 1
Hi - trying to figure out a formula that combines two columns into one long column, maintaining contents in multiple rows. Noting that the number of items in each column will change as these are each using the index/distinct to summarize a separate sheet. For example: Thanks for any help!