Introducing an OR statement within a COUNTIFS conditions succesfully

Adrià Termes

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!

Nick Korna

Does the OR condition only ever occur in 1 of the columns at a time? If so, you could do 3 COUNTIFS and add them together.

This can be done in one cell, though initially doing them one at a time will highlight any errors (making it easier to find).

Adrià Termes

https://community.smartsheet.com/discussion/comment/383684#Comment_383684

Hi Nick. No, the OR condition basically wants to capture the following:

• If the row name is in one, two or the three columns; the last condition (the OR one) inside the COUNTIFS is valid and is counted as a True.
• If the column name is not appearing in any of the three columns, it is a False and therefore, as COUNTIFS are nested AND statements, it will not be counted as it does not comply with the condition.

I get the COUNTIFS sum you say, and that is what I am applying right now. The problem with that is that, in case the name appears in the three columns, it counts thrice (Graphic example: This is for me like saying you had 3 dinners, when in reality you only had one dinner with three different dishes. Whenever you have one of the three dishes, you count as having dinner, and that is what the OR statement would allow me to introduce here. If I sum them up with 3 separate COUNTIFS, the numbers get inflated!).

I hope I helped you understand the issue, and if you have any idea or experience to help solve that, it will be more than welcomed!

Paul Newcome

You would have to do the three separate COUNTIFS added together, but if you want it to just count as a single entry, you can drop the calculation into an IF statement that says if the calc is greater than or equal to 1 then output 1.

=IF(COUNTIFS(.....) + COUNTIFS(.....) + COUNTIFS(.....)>= 1, 1)

Adrià Termes

https://community.smartsheet.com/discussion/comment/383812#Comment_383812

Hi Paul. Not working... Even outputting an error. I assume that the Countifs are more than just a boolean, so I would need to repeat that same operation over each row.

Paul Newcome

What error are you getting?