Formula Help - multiple criteria & ranges


Hi community!

I need HELP. I have been hitting my head against a wall trying to write a formula to calculate across different ranges and criteria. See photo for example of data. My formulas are not working out.

What I am trying to calculate is: Countifs

1) All PEM except PAC Support Center

2) Do not include rows with "opted out" checked

3) Any status except "connected"

4) kick-off is yes

5) risk is low

Thank you to any/all who can offer suggestions!


Best Answer


  • Samuel Mueller
    Samuel Mueller Overachievers

    Something like this should work:

    =COUNTIFS(PEM:PEM, <>"Support Center", [Opted Out]:[Opted Out], 1, Status:Status, <>"Connected", [Kick-off]:[Kick-off], "Yes", Risk:Risk, "Low")

    countifs has a criteria range, and then the criteria. The range is the column that has a criteria you want to count, and then the criteria for that range is what you want to count or not.

    Checkboxes are 0 or 1. does not equal is the <>.

    If you don't use an entire column for a range - all the criteria ranges musts be the same size.

    Does that help?

  • Taylar LaBonte
    Taylar LaBonte ✭✭✭
    Answer ✓

    @Samuel Mueller I don't know how to thank you!! It worked! I had no idea "<>" was even a thing. Problem solved - I should have posted this question sooner.

    Thank you!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!