# combine Countifs and OR

Options
✭✭✭✭✭

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

• Overachievers Alumni
Options

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)

• Overachievers Alumni
Options

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)

• ✭✭✭✭✭
Options

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.

• Overachievers Alumni
Options

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.

• Overachievers Alumni
Options

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)

• ✭✭✭✭✭
Options

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)

• Overachievers Alumni
Options