How do I add additional criteria to formula?

Sara Ross

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,

Sara

Amber Eakin

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: https://help.smartsheet.com/function/or.

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

I hope that helps!

Amber Eakin

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: https://help.smartsheet.com/function/or.

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

I hope that helps!

Sara Ross

Awesome! That captured what I wanted. I tried similar formulas, but I must have not had the syntax quite right.

Much appreciated!

Amber Eakin

My pleasure, @Sara Ross ! I'm glad it worked for you.