Formula to Count Consecutive Blank Cells in a Range Depending on Today's Date

I have been attempting to wrap my head around this for a week now to no avail. I've attempted searching, but either couldn't find a similar problem or couldn't describe my situation accurately enough to find anything useful. Hopefully someone can help!

In my spreadsheet, H2:BF2 contains the date of every Monday formatted as 01/13/2014. I am looking to count the number of consecutive blank cells in row 3 that is within a range between 01/13/2013 and the last Monday that just passed closest to TODAY().

Row 3 is going to contain a simple X. I am tracking how often a user attends a certain class. This class takes place every Monday from now until year end. Each time someone attends, that column for the date the session is held will be marked with an X. I am trying to write a formula which will count the number of consecutive blank cells in a range from 1/13/2013 only through the last class held. E.g., if today were Tuesday March 4th, I would want the formula to only go from H3:O3 (since cell O2 has a value of "3/3/2014").

The ultimate goal is to be notified if someone missed 3 classes in a row. However, if I just count consecutive blank cells like in the following formula:

Please Login or Register  to view this content.

Then it gives me 51, since it is counting all the way through the end of the year. Is there anyway to create a formula accomplishing the same goal, but checking H2:BF2, picking the closest date that has already passed, then ending the count there?

I have attached a sample workbook to clarify
Sample-Workbook.xlsx

Originally Posted by tophatpete

Thank you very much for your reply! I appreciate you taking a look at the problem I'm having. Unfortunately, that first formula does not seem to work perfectly. I moved the dates back that started in H1 to be able to test the formula out. As you can see from this screenshot below, there are three consecutive blank cells, but it only counts 1:

Attachment 290748

The formula does work at not counting past Today's date, which is helpful. Any ideas why it seems to be only counting 1?

Thank you.

It seems correct to me, try refer back to post #2 which I mentioned this for the first formula
"In column F you are trying to get the count of consecutive 3 blank cells which can be computed via following formula (your CF formula will have to be changed from 3 to 1 because each of 3 consecutive blank cells will be counted as 1)"

Thus if you have consecutive 4 blank cells, that means some one had missed 2 times because there were 2 occurrences of 3 consecutive blank cells.



_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

Hi,

No problem. I have re-uploaded the sample worksheet. In the sample, Column F is currently reading what I would like it to once the formula is created. In Row 2, the student has missed 3 consecutive classes. In row 3, although the student has missed 3 classes total, I'd like it to only read 1 since he/she never missed more than 1 class at a time. Row 4 should read 2 since the student missed a maximum of 2 consecutive classes (even though they missed a total of 4 classes).

Hopefully this clears up the confusion a little bit!

Sample-Workbook.xlsx

Hmm... what if he/she attended/missed classes like x;-;x;-;-;x;x;x;-;-;x would you expect a return of 1 or 2 for such cases?

ps: due to various possibility of attend/miss a class, that's why I propose a solution where it will count 1 whenever 3 consecutive blanks were found (which meets your ultimate goal)

ps: you might also want to try out other formulas in post #2 that will tell you which first/last class that the student missed out 3 times consecutively



_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

HI there

i need help to count the consecutive 3 empty cells