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,

Sara

Find more posts tagged with

Accepted answers

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!

All comments

Amber Eakin

=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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.