Counting Blank Cells

Beronica Muller

@Paul Newcome Yes! Interesting request right?

Paul Newcome

@Beronica Muller Sorry it took me so long to figure out what you wanted. Check out the below...

For a specific month (June of 2021 in the example):

=COUNTIFS(Complete:Complete, @cell = 0, [Files Processed]:[Files Processed], @cell = "", [Date Files Received]:[Date Files Received], @cell = "", [Date File Expected]:[Date File Expected], AND(IFERROR(MONTH(@cell), 0) = 6, IFERROR(YEAR(@cell), 0) = 2021))

And for a specific week (week 6 of 2021 in the example):

=COUNTIFS(Complete:Complete, @cell = 0, [Files Processed]:[Files Processed], @cell = "", [Date Files Received]:[Date Files Received], @cell = "", [Date File Expected]:[Date File Expected], AND(IFERROR(WEEKNUMBER(@cell), 0) = 6, IFERROR(YEAR(@cell), 0) = 2021))

Beronica Muller

@Paul Newcome for some reason, I am getting the #UNPARSEABLE error. :-(

Paul Newcome

@Beronica Muller Double check that the column names are spelled correctly. If all column names are spelled correctly, can you copy/paste the formula directly from the cell to here?

Beronica Muller

@Paul Newcome it worked. Thanks so much.

Would I use a similar formula for counting daily totals as well? Yeah, now I have been tasked for daily totals for the past 7 days. I think this is the formula, however, my spreadsheet results have a total of 2 for June 7th and my formula shows '0'. Please see below.

=COUNTIFS({Accrue Funding/Enrollment File Tracking Range 4}, @cell = 0, {Accrue Funding/Enrollment File Tracking Range 5}, AND(IFERROR(WEEKDAY(@cell), 0) = 158, IFERROR(YEAR(@cell), 0) = 2021))

Accrue Funding/Enrollment File Tracking Range 4 references the Completed column (checkbox)

Accrue Funding/Enrollment File Tracking Range 5 references the Date File Expected column (date)

Paul Newcome

To count for the past 7 days going back from TODAY, the criteria would be...

@cell >= TODAY(-7)

jmo

https://community.smartsheet.com/discussion/comment/249342#Comment_249342

@Paul Newcome should we always subtract 10 rows from any COUNTIF/IFS are blank? Is that a best practice? Are there scenarios where subtracting the additional 10 rows wouldn't be necessary or prudent?