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:
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?Please Login or Register to view this content.
I have attached a sample workbook to clarify
Sample-Workbook.xlsx
Bookmarks