Formula double-counting

Hello, hoping someone can help me rewrite the formula below:

=COUNTIFS({Certification Name-Primary}, "NSE2", {Reviewed&Approved}, true, {At risk check}, true, {Expiring-Expired}, true)

Right now it's only counting if all things are true but what I want is for the count to be limited to if the "at risk check" OR "expiring-expired" is true, not both. I tried to just break up the formula into two columns but ran into the next issue. There are several instances where both "at risk" and "expiring" are checked. Can I get it to not double-count those instances?

Thank you to anyone who can help!

Answers

  • Jason Tarpinian
    Jason Tarpinian ✭✭✭✭✭✭

    I think you would just need to expand the formula to count each value separately, then subtract them together. So something like:

    =COUNTIFS({Certification Name-Primary}, "NSE2", {Reviewed&Approved}, true, {At risk check}, true) +

    COUNTIFS({Certification Name-Primary}, "NSE2", {Reviewed&Approved}, true, {Expiring-Expired}, true) -

    COUNTIFS({Certification Name-Primary}, "NSE2", {Reviewed&Approved}, true, {At risk check}, true, {Expiring-Expired}, true)

    So the first two parts will calculate the individual check boxes, then the third part would subtract the instances where they are both checked.

    Jason Tarpinian - Sevan Technology

    Smartsheet Aligned Partner

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!