Counting blank values in one cell but only if a non blank value exists in another cell

Options

Good afternoon,

I'm trying to create a sheet summary formula that counts the number of blank values for a cell, but only if there's an entry in the sheet because my original sheet summary was counting blank values when there wasn't an entry in the sheet. I've reviewed several posts and tried a couple of different formulas but I'm getting the unparseable error. Below are the two formulas I've tried; they're basically the same except for the IF vs IFS with Count.

Any help would be greatly appreciated.

=COUNTIF(isblank([Smartsheet Contact #]:[Smartsheet Contact #], "", [Student Pre-Qualified for BOLD?]:[Student Pre-Qualified for BOLD?] ISBLANK(@cell))

=COUNTIFS(isblank([Smartsheet Contact

#]:[Smartsheet Contact #], "", [Student Pre-Qualified for

BOLD?]:[Student Pre-Qualified for BOLD?] ISBLANK(@cell))

Thank you,

Nancy

Best Answer

  • Cory Page
    Cory Page ✭✭✭✭✭
    Answer ✓
    Options

    I just deleted some data in one of my sheets and used:

    Using a helper column:

    =COUNTIF(Date@row, "")

    It seems to count the blank values but stops at the very last row that doesn't have data? Maybe this is not what you're looking for but I figured I would post just in case.

    Then you would just sum that helper column.

    If you dont want a helper column you could do this

    =SUM(Date:Date, COUNTIFS(Date:Date, "", [Primary Column]:[Primary Column], <>""))

    Using the countifs statement will let you add a second criteria in this case I picked the primary column thinking there should always be data. But you can pick whatever column always has data for the 2nd criteria.

    Hope this helps.


Answers

  • Cory Page
    Cory Page ✭✭✭✭✭
    Answer ✓
    Options

    I just deleted some data in one of my sheets and used:

    Using a helper column:

    =COUNTIF(Date@row, "")

    It seems to count the blank values but stops at the very last row that doesn't have data? Maybe this is not what you're looking for but I figured I would post just in case.

    Then you would just sum that helper column.

    If you dont want a helper column you could do this

    =SUM(Date:Date, COUNTIFS(Date:Date, "", [Primary Column]:[Primary Column], <>""))

    Using the countifs statement will let you add a second criteria in this case I picked the primary column thinking there should always be data. But you can pick whatever column always has data for the 2nd criteria.

    Hope this helps.


  • Nancy JOnes
    Options

    Hi Cory,

    That worked. Thank you so much!

    Have a great day,

    Nancy

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!