combine Countifs and OR
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)))
Best Answer

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

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)

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

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.

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)

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)

Awesome, glad it's working.
Help Article Resources
Categories
Check out the Formula Handbook template!