combine Countifs and OR

Nate Ensor

Hi - Can't quite get this formulas to work. Not sure if I have it set up correctly.

I need a box to be checked if one of two scenarios exist.

If the E# is found AND either "UCA approved" or "approved for deployments" is checked.

=IF(COUNTIFS({UCA E#}, [E #]@row, AND(OR({UCA approved}, true, {approved for deployments}, true) > 0, true, false)))

David Tutwiler

Could you break it up into 2 formulas? For instance:

=IF(COUNTIFS({UCA E#}, [E #]@row, {UCA approved}, true) + COUNTIFS({UCA E#}, [E #]@row, {approved for deployments}, true) > 0, true, false)

David Tutwiler

I think you are really close. I think that the AND in your statement is redundant and causing you issues. When you use COUNTIFS, the next IF statement is assumed AND and isn't necessary to call out. Can you try:

=IF(COUNTIFS({UCA E#}, [E #]@row, OR({UCA approved}, true, {approved for deployments}, true)) > 0, true, false)

Nate Ensor

that definitely makes sense and looks better, but I got an #InvalidDATATYPE error. I re-did the references just in case, but it didn't change.

David Tutwiler

I can see why now. The COUNTIF function works like this:

Criteria range #1, Criteria2, Criteria range #2, Critieria2

We've only given it the criteria where it is asking for a range in the second statement. I think this will have to be rethought as the OR is only really useful in a COUNTIF when you're looking for 2 potential answers in a dropdown list.

David Tutwiler

Could you break it up into 2 formulas? For instance:

=IF(COUNTIFS({UCA E#}, [E #]@row, {UCA approved}, true) + COUNTIFS({UCA E#}, [E #]@row, {approved for deployments}, true) > 0, true, false)

Nate Ensor

ok thanks, here's where I ended and it's working. so yea, two formulas. thanks for your help!

=IF(OR(COUNTIFS({UCA E#}, [E #]@row, {EPU approved}, true) > 0, COUNTIFS({UCA App E#}, [E #]@row, {approved for future}, true) > 0), true, false)

David Tutwiler

Awesome, glad it's working.