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
-
@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))
-
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
-
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
-
Saw that when searching the forums. Tried mine and yours, but no joy.
-
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?
-
@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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!