Pesky COUNTIFS with OR formula

Jaime Ciabattoni

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 1-3 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

Leibel S

@Jaime Ciabattoni

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"))

Leibel S

@Jaime Ciabattoni

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"))

Jaime Ciabattoni

@Leibel S

Thank you for your help!!!!