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
Comments
-
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.
-
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.
-
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.
-
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 -
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.
-
Original Equation
=COUNTIFS({Perceptive Content Requests Range 1}, "Open" OR "Awaiting Addl Info" OR "In Progress", {Perceptive Content Requests Range 3}, "Operations")
To
=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.
Help Article Resources
Categories
Check out the Formula Handbook template!
