How to create a countif formula with OR

SJTA

I am trying to create a formula across columns with different values in a cell. I have the formula for one value, Preliminary Draft. This works fine.

=COUNTIFS({Comments Log Range 1}, [Document Name]@row, {Comments Log Range 2}, "Preliminary Draft", {Comments Log Range 4}, "Open")

However, the value in range 2 can also be "Intermediate", or "Final".

This is what I tried:

=COUNTIFS(({Comments Log Range 1}, [Document Name]@row), OR({Comments Log Range 2}, "Intermediate", {Comments Log Range 2}, "Preliminary Draft", {Comments Log Range 2}, "Final"), {Comments Log Range 4}, "Open"))

Smartsheet says its UNPARSEABLE.

I am sure it's a simple solution, but I can't figure it out. Thanks.

Carson Penticuff

Try this one.

=COUNTIFS(({Comments Log Range 1}, [Document Name]@row), OR({Comments Log Range 2} = "Intermediate", {Comments Log Range 2} = "Preliminary Draft", {Comments Log Range 2} = "Final"), {Comments Log Range 4} = "Open"))

SJTA

Still returns as unparseable

Carson Penticuff

My apologies, I didn't notice your last condition was not inside the OR(). Try this one

=COUNTIFS({Comments Log Range 1}, [Document Name]@row, OR({Comments Log Range 2} = "Intermediate", {Comments Log Range 2} = "Preliminary Draft", {Comments Log Range 2} = "Final"), {Comments Log Range 4}, "Open")

SJTA

Now the error is 'invalid operation'.

Carson Penticuff

=COUNTIFS({Comments Log Range 1}, [Document Name]@row, {Comments Log Range 2}, OR(@cell = "Intermediate", @cell = "Preliminary Draft", @cell = "Final"), {Comments Log Range 4}, "Open")

CMoore

Hi,

Thanks for the suggestion, @Carson Penticuff . However, I was unable to make the @cell work. Did it work for anyone else?

Thanks for any suggestions.

Carson Penticuff

I have it working here: