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

Options
Tknouse
Tknouse
edited 06/16/22 in Formulas and Functions

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!

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!