Counting consecutive zeros at end of range

Hi, all. I'm embarrassed to be having this issue, but my brain simply isn't working this morning. I have the attached spreadsheet, which is going to be an attendance sheet for a group I'm in. The problem is in the Summary tab, column L. I want to figure out the number of consecutive times a person has missed coming to group events.

The wrench in this is that there are three types of events: one on Sunday, one on Tuesday, and other events that can happen any day of the week. I need to be able to count the Sunday and Tuesday ones separately, but also want an option for all three types to be considered together; hence the dropdown in O1.

For this exercise, the numbers are hard-coded into B2:G5, but they will be the results of formulas going forward. Also, my intention is that for each new event, a column will be inserted after G, so any formula will have to include an ability to grow with the sheet. Thanks for your help! I'm glad there are people on here much better than I am at this.

Perhaps you could try:

Please Login or Register  to view this content.

edit: scrap that - misread - the above returns the max no. of consecutive non-appearances - which is not what you asked for !

psumvp: Thanks for the thought. I might have to discuss with the end user (I don't know his abilities with excel or preferences for this). It certainly would make it easier if he accepts.

MarvinP: I read the intro and layout sections and it seems I'm following most of those already. The pivot table is another good thought to consider; I just frankly really don't like the layout Excel has. Plus, it doesn't solve the issue of how many consecutive periods someone has missed.

DO: Thanks for the attempt. Maybe someone will be able to build off it and solve it. I'd be ok with a helper column or two, if that makes things easier.

Thanks again, everyone, for your input!

If I've understood the approach I would adopt would be to insert a new row 1 into which I would add a 1/0 flag - generated by:

Please Login or Register  to view this content.

At which point - assuming XL2007 - you could perhaps use:

Please Login or Register  to view this content.

In hindsight the formula for 3 could be shortened to:

Please Login or Register  to view this content.

edit: brain catching up... given H is blank the initial test is not strictly necessary

Please Login or Register  to view this content.

DO, that does it!!! You, sir, never cease to amaze. Thanks a lot!

Thanks though I only wish I could get out of the habit of posing a gazillion formulas as I work my head around it !

In reality it is the addition of the 1/0 header row that simplifies things along with the fact you can utilise column H (blank).

With row 1 in place it's then a case of finding the last column (B:H) where both header row & current row are 1 (ie last appearance on valid day), offsetting by one column to the right and summing the remainder of row 1 (to H)
ie valid dates where attendance is 0

If there are no attendances the IFERROR ensures the start point of the range summed is the very first column (B) so row 1 is summed in entirety (B:H)

If the last date column (G) happens to be valid with an appearance then the Sum is of H1 which we know to be blank thus ensuring result of 0.