using OR in a formula

Hello Smartsheet Community,

I'm looking for some help in utilizing the OR statement within a function which references columns in another sheet. I am trying to count how many rows have a request status of either "Open", "Awaiting Addl Info", or "In Progress" from specific departments who submit these requests. I seem to be entering the wrong syntax here because I keep getting an "Unparseable" error message.

Here is my formula:

=COUNTIFS({Perceptive Content Requests Range 1}, "Open" OR "Awaiting Addl Info" OR "In Progress", {Perceptive Content Requests Range 3}, "Operations")

Can anyone spot what this novice might be doing incorrectly?

Thanks so much,

Michelle

Find more posts tagged with

Education

Formulas

IT & Operations

Comments

p_bureau

Hi,

in a COUNTIF formula, you cannot use OR. You have to add many COUNTIF formulas like below :

=COUNTIFS({Perceptive Content Requests Range 1}, "Open", {Perceptive Content Requests Range 3}, "Operations") + COUNTIFS({Perceptive Content Requests Range 1},"Awaiting Addl Info", {Perceptive Content Requests Range 3}, "Operations") + COUNTIFS({Perceptive Content Requests Range 1},"In Progress", {Perceptive Content Requests Range 3}, "Operations")

Does this help ?

Best regards,

Paul.

Michelle Comitor

Thank you Paul. The formula works as-is, and I appreciate your help.

I wonder if there is another way to condense the formula? Especially if we needed to edit this down the line, it would be much easier if we only needed to scan through two different criteria as opposed to several formulas/criteria that are pieced together. Really hoping for a way to refine this function, if possible, to collapse the three Status criteria into one, simplified function that looks for the existence of multiple values.

Otherwise, the formula you provided does work just fine.

p_bureau

Inside a COUNTIF (and every xxxIF formula), I'm pretty sure this is the only way to achieve what you are looking for.

However, you could add a new column in your data sheet, then perform the COUNTIF on this column.

This seems a bit too much for me but you could :

create a new sheet "Parameters" with only one column STATUS and 3 rows (linked picture)
in your " Perceptive Content Requests" sheet, create a new column STATUS_CALC with the formula : =IF(AND([Column x]@row = "Operations",COUNTIF({Parameters Range 1}, [Column y]@row)>0),1,0)
you can then create a simple =SUM({ Perceptive Content Requests Range 4}) to get the correct value.

Note : {Parameters Range 1} is a crossshet reference to the column STATUS of the sheet "Parameters".

Now you can simply edit the values in the "Parameters" sheet, even add some values!

However the main drawback is that since there is not (yet) calculated formula in Smartsheet, you could have some issues with the formula in the STATUS_CALC column.

Could this fits your need ?

Best regards,

Paul.

Michelle Comitor

Paul, thank you for the alternative.

Your first solution will definitely fit our needs and seems a lot less clunky than the alternative method.

Again, thanks for taking the time to walk through this. It is much appreciated.

Best,

Michelle

Paul Newcome

How many other options do you have besides the 3 you have listed? If it is only one more ("Completed" for example) you could condense a bit by using a COUNTIF to count how many are Complete then subtract it from the total count.

=COUNT(Status:Status) - COUNTIF(Status:Status, "Complete")

Of course you would replace the range with your range and whatnot, but working backwards will give you the same result in a smaller formula and allow room for change.

The above will only work if you only have one other option. Otherwise, some more working may be required.

L_123

Original Equation

=COUNTIFS({Perceptive Content Requests Range 1}, "Open" OR "Awaiting Addl Info" OR "In Progress", {Perceptive Content Requests Range 3}, "Operations")

=Count(Collect({Perceptive Content Requests Range 1},{Perceptive Content Requests Range 1},Or(@cell = "Open",@cell = "Awaiting Addl Info",@cell = "In Progress"),{Perceptive Content Requests Range 3}, "Operations"))

Collect returns a group of values based on criteria in an array. Count will count how many values are inside of that array. Collect is the most versatile formula in smartsheet in that it accepts all data types and conditional logic, and even allows for conditional ranges in less complex formulas.

https://help.smartsheet.com/function/collect

Sign in to join the conversation!

Quick Links

Trending discussions

Match or Index
I am trying to pull a phone number and email from our master roster that is on another sheet in Smartsheet. I cannot figure out the formula. This is the formula I have tried. =INDEX([EMPLOYEE NAME]@row , MATCH({IMS Master Roster Range 2}), 0)
Workflow Generate Impact Report PDF in Impact Form Response Sheet has become invalid
Hi All, I keep getting this annoying error when I try to trigger a PDF generation. I initially set up to trigger the PDF generation to kick off once the last field the needs data is "not blank". That is when the error started. I modified the sheet and added a yes/no drop down to trigger the PDF generation when the value in…
INDEX COLLECT MIN
Desired result is to pull the Initiative Name where the Days to Due Date is the least (so, due the soonest); then to make an index for the Top 10 Initiatives Due Soonest Currently using this formula: =INDEX(COLLECT({Project Master - Initiative}, {Project Master - Days to Due}, MIN({Project Master - Days to Due})), 1) This…