How to create a COUNTIFS 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.

Paul Newcome

Try this instead:

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

Kleerfyre

So I have a formula that I can re-work that will help you in this situation, but you might be getting the error error due to the first part of your formula. Is there a match of what you have in the Document Name@row in the formula sheet that matches what you have in the Comments Log Range 1 reference of the other sheet. Also, I recommend that you rename your cross sheet references to show what data you are referencing instead of just Range 1 and so on. It will help you in the long run when creating formulas with cross sheet references. This is an example of one of my cross sheet references for an idea:

{2023-05 Histscan Source} 2023-05 Histscan in the name of my sheet being referenced and Source is the name of the referenced column.

Let me know any other details of this formula and I can use your information to create a formula based on the one I already have that will help you in this situation.

Kleerfyre

And you can try this formula and see if it works for you:

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

SJTA

@Paul Newcome Thanks, but that returned as an invalid operation

Paul Newcome

Try @cell references.

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

If that doesn't work, can you provide a screenshot of the formula open in the sheet similar to the screenshot below?