COUNTIF formula with ISBLANK help

Options

Hello! I keep getting confused with these countif and blank formulas :( would appreciate some guidance. I am looking to create a formula that pulls from another sheet that counts all the sites that have an assigned number BUT has the "inactive status" as blank.

Essentially only counting the active sites with an assigned number.

=COUNTIF({Site Number}, {Master Data | Inactive Status}, ISBLANK, <>"")

I know it's wrong especially after the ISBLANK but I'm not sure what to add after that. Thanks in advance!

Answers

  • Peggy Parchert
    Peggy Parchert ✭✭✭✭✭✭
    edited 06/07/23
    Options

    @laura.sandoval

    Hello - would this work?

    =COUNTIF({Site Number}, {Master Data | Inactive Status} <> "")

    Thanks -Peggy

  • laura.sandoval
    Options

    @Peggy Parchert

    It's still counting the ones that have an inactive status :(

    =COUNTIF({Site Number}, {Master Data | Inactive Status} <> "")


    This is what I have...not sure if I'm missing something.

  • Eric Law
    Eric Law ✭✭✭✭✭✭
    Options

    @laura.sandoval

    Hello Laura!

    So the COUNTIF formula only has 2 things to it, the criteria and the range. If you want 2 criteria, you will need the COUNTIFS function.

    For the ISBLANK function, you just need to point it to a cell, so it would be something like =ISBLANK(@cell) or the not =NOT(ISBLANK(@cell))

    Just to clarify, if Inactive Status is not blank, would you always count it? Or will you need something in the site number as well? Just to streamline the formula.

    Try, =COUNTIF({Master Data | Inactive Status}, ISBLANK(@cell)) if you are only looking at the Inactive Status

    Or, =COUNTIFS({Site Number}, NOT(ISBLANK(@cell)), {Master Data | Inactive Status}, ISBLANK(@cell)) if you need both Site Number not blank and Inactive Status blank.

    Hope that helps

  • laura.sandoval
    Options

    @Eric Law

    This is so helpful thank you!! I think the best fit will be the second one because I need both Site Number not blank and Inactive Status blank. Silly question...what is the @cell referring to exactly?

    =COUNTIFS({Site Number}, NOT(ISBLANK(@cell)), {Master Data | Inactive Status}, ISBLANK(@cell))

  • Eric Law
    Eric Law ✭✭✭✭✭✭
    Options

    @laura.sandoval @cell just means it's looking at each cell individually within the range you are specifying. You can use the ="" and <> "" if that makes more sense to you.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!