Can anyone spot an error in this formula?
Hi all, I'm having trouble combining <>s in the OR function.
Using the formula below I receive one answer:
=(COUNTIFS({TACTIC actual delivery date}, >=(DATE(2019, 1, 1)), {TACTIC actual delivery date}, <=(TODAY()), @{TACTIC tactic status}, OR(@cell <> "Cancelled", @cell <> "On hold")))
While editing the formula to split out the "is nots" into two separate parameters gives me another answer (the correct one, when I manually counted to double check):
=(COUNTIFS({TACTIC actual delivery date}, >=(DATE(2019, 1, 1)), {TACTIC actual delivery date}, <=(TODAY()), {TACTIC tactic status}, <> "Cancelled", {TACTIC tactic status}, <> "On hold")))
Am I using the <>s in OR incorrectly? Or is this a bug? I'm using this in many formulas across a roll up sheet so want to be sure before I go ahead and change each instance to split them out instead of combining using OR.
Comments
-
Switch the OR to AND. The OR function will look to see if it is either on or the other which means the "On Hold" will be counted since it is not "Cancelled" and the other way around.
Using the AND function says that it cannot be "On Hold" and it cannot be "Cancelled". That would be the function to use if you are trying to exclude both.
When using COUNTIFS (and SUMIFS for that matter), you are essentially using a big AND statement. COUNT this if ALL of these are true which is basically what the AND function does: if ALL of these are true.
-
Seems so simple Paul, and yet here I was using the wrong function even still. Many thanks, I will edit these! Now to find where I've used OR in other dashboards/roll-ups instead of AND...
-
No worries. Happy to help!
Help Article Resources
Categories
Check out the Formula Handbook template!
