# Thread: Count Blank Cells by running date series

1. ## Count Blank Cells by running date series

I have a spreadsheet with dates in row 1 across the top that errors occurred at a number of cash registers, with the register numbers listed down Column A. Below each date is an "X" for each register that had an error that day. I need to keep track of how many errors occurred on each register in the last 90 days, so want to count the nonblank cells in each row for the columns for only the last 90 days, based on the TODAY()-90 value. Playing with COUNTA, COUNTIF, COUNTIFS functions, but can't figure out how to test the date in Row 1 while using one of the various COUNT functions in each register row below to count the nonblank X cells. I can see the simple way would be to replace the X's with the date and use CountIF, but I'm curious how to do the first option with the X's.

Thanks
Steve

2. Gordon,

Try something like this where B1 to Z1 is the row range of dates and B2 to Z2 is the row for register 1 containing the X. Place the formula in cell AA2

=COUNTIFS(B1:Z1,">"&TODAY()-90,B2:Z2,"<>"&"")

The count for the last 90 days would be the number of X's beyond 10/5/2014.

Countifs3.png

This could also be done using a better method that counts only an X's instead of non-blank cells.

=COUNTIFS(B1:Z1,">"&TODAY()-90,B2:Z2,"=X")

HTH,
Maud

3. Originally Posted by Maudibe
Gordon,

Try something like this where B1 to Z1 is the row range of dates and B2 to Z2 is the row for register 1 containing the X. Place the formula in cell AA2

=COUNTIFS(B1:Z1,">"&TODAY()-90,B2:Z2,"<>"&"")

The count for the last 90 days would be the number of X's beyond 10/5/2014.

Countifs3.png

This could also be done using a better method that counts only an X's instead of non-blank cells.

=COUNTIFS(B1:Z1,">"&TODAY()-90,B2:Z2,"=X")

HTH,
Maud

Thanks, either of those work. Now is see how the COUNTIFS works. Nice

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•