Need help with Counting blank cells in a column.

I need to count the number of blank cells with in a column on another sheet. This is a cross sheet formula where the sheet with the data has a column containing dates. I need to keep a count on the number of cells within in that column that have blank cells(no date) for a particular date range.

The following formula was working for about 5 days and is no longer returning blank cells. No change has been made to either the formula or the data sheet.

=COUNTIFS({Device & Service Checks Date of Closeout}, >=[Start Date]1, {Device & Service Checks Date of Closeout}, <=[End Date]1, {Device & Service Checks Date of Closeout}, "")

Best Answers

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer βœ“

    @Paul Newcome, you genius! There's no way it could work if it's saying that it has to be between those two dates AND blank.

    @Brandon Williams If you're wanting to check for only blank cells within the Device & Service Checks Date of Closeout column, you'll do this:

    =COUNTIF({Device & Service Checks Date of Closeout}, isblank(@cell)).

    If there's a different column that you want to count blanks in, but with dates in the Device & Service Checks Date of Closeout column during the time frame you've laid out, you'd do this:

    =COUNTIFS({Device & Service Checks Date of Closeout}, >=[Start Date]1, {Device & Service Checks Date of Closeout}, <=[End Date]1, {Other column range}, isblank(@cell))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer βœ“

    Happy to help. πŸ‘οΈ


    Side note: I personally use

    = "" (<-- That's a double set of quotes/Not 4 apostrophes)

    instead of

    ISBLANK(......)


    It isn't too bad in this particular use case, but some other formulas get a little crazy with parenthesis, so I try to cut them out whenever I can (not to mention saving a couple of keystrokes).


    I also use

    <> ""

    vs

    NOT(ISBLANK(.........))

Answers

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭

    Hi Brandon,


    Try this:

    =COUNTIFS({Device & Service Checks Date of Closeout}, >=[Start Date]1, {Device & Service Checks Date of Closeout}, <=[End Date]1, {Device & Service Checks Date of Closeout}, isblank(@cell))


    If it was previously working and is no longer, I'm not sure if this will fix it...but let me know if it works!


    Best,

    Heather

  • Brandon Williams
    edited 04/07/21

    Saw that when searching the forums. Tried mine and yours, but no joy.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I'm not sure exactly how it was working in the first place. If it is blank, how can it also be between two dates?

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer βœ“

    @Paul Newcome, you genius! There's no way it could work if it's saying that it has to be between those two dates AND blank.

    @Brandon Williams If you're wanting to check for only blank cells within the Device & Service Checks Date of Closeout column, you'll do this:

    =COUNTIF({Device & Service Checks Date of Closeout}, isblank(@cell)).

    If there's a different column that you want to count blanks in, but with dates in the Device & Service Checks Date of Closeout column during the time frame you've laid out, you'd do this:

    =COUNTIFS({Device & Service Checks Date of Closeout}, >=[Start Date]1, {Device & Service Checks Date of Closeout}, <=[End Date]1, {Other column range}, isblank(@cell))

  • Thank You! This is working.

    =COUNTIF({Device & Service Checks Date of Closeout}, ISBLANK(@cell)) - 10

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer βœ“

    Happy to help. πŸ‘οΈ


    Side note: I personally use

    = "" (<-- That's a double set of quotes/Not 4 apostrophes)

    instead of

    ISBLANK(......)


    It isn't too bad in this particular use case, but some other formulas get a little crazy with parenthesis, so I try to cut them out whenever I can (not to mention saving a couple of keystrokes).


    I also use

    <> ""

    vs

    NOT(ISBLANK(.........))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!