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
 10.5K Get Help
 61 Global Discussions
 46 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 54 Brandfolder
 124 Just for fun
 50 Community Job Board
 466 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!