Introducing an OR statement within a COUNTIFS conditions succesfully

Adrià Termes
Adrià Termes ✭✭✭
edited 07/05/23 in Formulas and Functions

Hello community!

This question may seem basic, but I would say is a common headache across users. I am trying to do a COUNTIFS function that takes into account that a specific name is in either one of three columns (as COUNTIFS create AND statements between the conditions). I introduced an OR statement at the end for that, in hopes of working. The problem?: UNPARSEABLE or INVALID OPERATION or INVALID DATA TYPE, as the condition applied to three columns does not work! Below you can check the code with the OR condition highligfhted in bold:

COUNTIFS({Referencepage 1}; =Status$22; {Referencepage 10}; Referencepage 10} <> 1; {Referencepage Rango 2}; ="Initiative"; OR({Referencepage Range 21}; =[Metric NAme]@row; {Referencepage 23}; =[Metric NAme]@row; {Referencepage Rango 3}; =[Metric NAme]@row))

As you can see, I introduced there the OR statement checking whether if in any of the three columns we have a specific name (denoted with Metric NAme]@row) , then we count the condition as ok inside the COUNTIFS. The situation is so unmanageable that I needed to sum up 3 different COUNTIFS, one for each column as the last condition, as the errors keep coming in (which is not an OR statement at all).

Does anybody have any idea on why this is constantly failing? I would say I checked the documentation and that the OR statement embedded within the last condition of the COUNTIFS is ok, but I literally see no way out to introduce such OR so that it works whether the name (denoted with Metric NAme]@row) is in one of the columns!

If you have any idea or suggestion, that would massively help!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!