Nesting OR Function in COUNTIF

Bryan Letourneau

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!

Genevieve P.

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

Mary Royston

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

Bryan Letourneau

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.

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

Bryan Letourneau

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.

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

Bryan Letourneau

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!