Nesting OR Function in COUNTIF

Hi Community,

I'm running into an error while nesting my OR function and I can't seem to identify a solution and would appreciate some help! Please see below:

Thanks!

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Bryan Letourneau

    You would still list the entire column in the range, {January 2022 Range3}, but then the @cell is specific to the instructions in the OR function. It says to "check each cell" within that range.

    However I believe for your purposes adding multiple COUNTIFS together will be the best way forward. You're receiving an error in the image above because of the comma between the two COUNTIFS.

    It should be:

    =COUNTIFS(formula) + COUNTIFS(formula)

    Adding the two together without any commas between. Try this and let me know if it works!

    Cheers,

    Genevieve

Answers

  • Hey @Bryan Letourneau

    You almost got it, but you just have your OR in the wrong spot, and you need to add @cell in font of each value, so it knows to search each cell. Try something like this and see if it works:

    =countifs({January 2022 Range3} "CM", {January 2022 Range 4}, OR(@cell = "FD-xxx", @cell = "FD-xx", @cell = "FD blah"))

    Hope that helps, let me know if you need any other help :)

  • Hi Mary,

    Thank you for responding. I'm still receiving an invalid operation error with those changes 😕

    I've scanned through and don't believe I'm missing commas, spaces, or parentheses?

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Bryan Letourneau

    In your second screen capture it doesn't look like you used the @cell function instead of {the range} repeated:

    OR(@cell = "This", @cell = "That")

    If this hasn't helped, it would be useful to know more about the source sheet. Is your {January 2022 Range 4} column multi-select or a text/number column?

    I would recommend adding multiple COUNTIFS together instead of using an OR function. For example:

    =COUNTIFS({January 2022 Range3} "CM", {January 2022 Range 4}, "FD-xxx") + COUNTIFS({January 2022 Range3} "CM", {January 2022 Range 4}, "FD-xxx") + COUNTIFS({January 2022 Range3} "CM", {January 2022 Range 4}, "FD-xxx")

    Let me know if either of these have worked for you!

    Cheers,

    Genevieve

  • Hi Genevieve,

    Thanks for the response. I'm unsure what you mean by the @cell function because I'll need to reference an entire column as opposed to specific cells. That column is formatted as free text and I feel the OR function should work but I can't identify why its not.

    Also, I tried the second formula you provided and still encounter the unparseable error


  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Bryan Letourneau

    You would still list the entire column in the range, {January 2022 Range3}, but then the @cell is specific to the instructions in the OR function. It says to "check each cell" within that range.

    However I believe for your purposes adding multiple COUNTIFS together will be the best way forward. You're receiving an error in the image above because of the comma between the two COUNTIFS.

    It should be:

    =COUNTIFS(formula) + COUNTIFS(formula)

    Adding the two together without any commas between. Try this and let me know if it works!

    Cheers,

    Genevieve

  • It seems it was a combination of that and needed commas after the ranges before the "CM". All squared away now, thank you so much for your patience!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!