How to incorporate an OR statement in a =COUNTIFS statement

Options
jmo
jmo ✭✭✭✭✭✭

Hi team - I have a formula that works for counting "errors" on from source data:

=COUNTIFS({Agreements out for Approval (C H M L) Exempt}, 0, {(Source Data) BDO Exempt}, 0, {Agreements out for Approval (C H M) main row}, 1, {Agreements out for Approval (C H M) Created}, IFERROR(MONTH(@cell), 0) = 10, {Agreements out for Approval (C H M) C/H/M incorrec}, 1, {Agreements out for Approval (C H M L) Low incorrec}, 1, {Agreements out for Approval (C H M) authors}, NOT(CONTAINS("gsd invalid request", @cell)))

But I need to have the formula return a count when an OR is incorporated, so =COUNTIFS:

  • {Agreements out for Approval (C H M L) Exempt}, 0 and
  • {(Source Data) BDO Exempt}, 0 and
  • {Agreements out for Approval (C H M) main row}, 1 and
  • {Agreements out for Approval (C H M) Created}, IFERROR(MONTH(@cell), 0) = 10 and
  • {Agreements out for Approval (C H M) C/H/M incorrec}, 1 OR
  • {Agreements out for Approval (C H M L) Low incorrec}, 1 and
  • {Agreements out for Approval (C H M) authors}, NOT(CONTAINS("gsd invalid request", @cell

Any ideas on how to do that seamlessly?

Thanks,

Jeff

Answers

  • Aya
    Aya ✭✭✭
    Options

    Hi Jeff—Can you try this one?


    =IF(COUNTIF({Agreements out for Approval (C H M) C/H/M incorrec}, 1)=0,(COUNTIFS({Agreements out for Approval (C H M L) Exempt}, 0, {(Source Data) BDO Exempt}, 0, {Agreements out for Approval (C H M) main row}, 1, {Agreements out for Approval (C H M) Created}, IFERROR(MONTH(@cell), 0) = 10, {Agreements out for Approval (C H M L) Low incorrec}, 1, {Agreements out for Approval (C H M) authors}, <>"gsd invalid request")),(COUNTIFS({Agreements out for Approval (C H M L) Exempt}, 0, {(Source Data) BDO Exempt}, 0, {Agreements out for Approval (C H M) main row}, 1, {Agreements out for Approval (C H M) Created}, IFERROR(MONTH(@cell), 0) = 10, {Agreements out for Approval (C H M) C/H/M incorrec}, 1, {Agreements out for Approval (C H M) authors}, <>"gsd invalid request")))


    Please let me know if that works.


    Thanks,

    Aya

  • jmo
    jmo ✭✭✭✭✭✭
    Options

    Hi @Aya - that definitely gives me something to work with! It didn't error out, so that's good, but the total is not what I was expecting. That's probably something missing on my side.

    Appreciate the prompt response.

    Jeff

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!