Pesky COUNTIFS with OR formula
Hi everyone,
I have one of those pesky formulas that is giving me a hard time. This is for a Summary Sheet I'll use to build a dashboard so I'm referencing another sheet with the data. Here's what I am trying to pull...Count if the following is true:
1) Project name is the same as the project in the summary sheet
2) Month is the same as the month in the summary sheet column
3) Year is the same as the year reference in the summary sheet
4) Status is not equal to "Completed" or "Canceled/Lost"
I have functioning formulas with 13 so I know they are correctly referencing as expected.
It's the 4th piece that is giving me issues. I do have a COUNTIF formula looking at the statuses which is working without issue. I've gone back and forth about AND vs OR but in both cases am getting the INVALID OPERATOR error.
Here's the formula as is:
=COUNTIFS({Project}, $Project@row, {Month}, Jan$17, {Year}, $Reference$14, OR({Status} <> "Completed", {Status} <> "Canceled/Lost"))
Here's a screenshot of my Summary Sheet.
Thank you in advance for your help!
~Jaime
Best Answer

See below how to use the OR and AND function:
=COUNTIFS({Project}, $Project@row, {Month}, Jan$17, {Year}, $Reference$14, {Status}, AND(@cell<> "Completed", @cell <> "Canceled/Lost"))
Answers

See below how to use the OR and AND function:
=COUNTIFS({Project}, $Project@row, {Month}, Jan$17, {Year}, $Reference$14, {Status}, AND(@cell<> "Completed", @cell <> "Canceled/Lost"))

Thank you for your help!!!!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.3K Get Help
 364 Global Discussions
 199 Industry Talk
 428 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 445 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!