How do I add additional criteria to formula?

I have a section in my sheet summary that counts the number of requests currently in process. The formula I used counts the item if the Request ID is not blank and the status is not "Completed". I would also like the formula to not include items with the status "Not Applicable". How would I revise the formula?

Current formula: =COUNTIFS(Status:Status, NOT(@cell = "Completed"), [Request ID]:[Request ID], NOT(ISBLANK(@cell)))

Additionally, I would like the Total Requests Received to not count those with the status "Not Applicable". Current formula: =COUNT([Request ID]:[Request ID])

Any guidance is much appreciated,


Best Answer

  • Amber Eakin
    Amber Eakin ✭✭✭✭✭✭
    Answer ✓

    It sounds like you want the formula to capture items where the Request ID is not blank and the Status is neither "Completed" nor "Not Applicable." Is that right? You want the OR function for this case:

    =COUNTIFS(Status:Status, NOT(OR(@cell = "Completed", @cell = "Not Applicable")), [Request ID]:[Request ID], NOT(ISBLANK(@cell)))

    I hope that helps!

    Amber Eakin, MSLS, M.Ed.

    Adult Education Specialist | Process Improvement Enthusiast


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!