Need help with Counting blank cells in a column.

Brandon Williams

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}, "")

Heather Duff

@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

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(.........))

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

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

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

@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))

Brandon Williams

Thank You! This is working.

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

Paul Newcome

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(.........))