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

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    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

  • David Tutwiler
    David Tutwiler Overachievers Alumni

    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@E
    Nate@E ✭✭✭✭✭

    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
    David Tutwiler Overachievers Alumni

    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
    David Tutwiler Overachievers Alumni
    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)

  • Nate@E
    Nate@E ✭✭✭✭✭

    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
    David Tutwiler Overachievers Alumni

    Awesome, glad it's working.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!