How to create a COUNTIFS formula with OR

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.

Tags:

Answers

  • Paul Newcome
    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
    Kleerfyre ✭✭✭✭✭✭
    edited 06/15/23

    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.

    Jonathan Sanders, CSM

    "Change is always scary because it is unknown, but facing the unknown is what makes us stronger."

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

    Jonathan Sanders, CSM

    "Change is always scary because it is unknown, but facing the unknown is what makes us stronger."

  • SJTA
    SJTA ✭✭✭✭

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

  • Paul Newcome
    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?


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!