How do you exclude a specific status from another sheet in a formula?

Hello!

I'm having trouble finding a way to exclude a status from this formula:

=IF([Total on PTO]@row < 2, "Under Limit", IF([Total on PTO]@row = 2, "At Limit", "Over Limit"))

It's referencing another sheet for PTO requests. The limit is including requests that have been withdrawn, but we don't count those as the actual number of people on PTO.

On the request sheet the formula is referencing, there is a column titled Status and one of the options is Withdrawn. I'm not sure what to put in the formula so it will exclude the withdrawn statuses.

Any help is appreciated!

Find more posts tagged with

Sheets

Formulas

Accepted answers

All comments

Genevieve P.

Hi @Tknouse

The adjustment would need to be made to the formula that's in your [Total on PTO] column. Do you know what this formula is?

It sounds like it might be a COUNTIF formula or COUNTIFS (plural).

If it's COUNTIF, you'll just want to add the S to the end and then add in another cross-sheet range (the Status column in your other sheet) and another criteria (that the status is NOT "Withdrawn").

E.g:

=COUNTIFS({Column 1 range}, "First Criteria", {Status Column}, <> "Withdrawn")

This will exclude the Withdrawn column from the count in your [Total on PTO] cell, so that your current formula will calculate correctly.

Cheers!

Genevieve

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.