Excel 2007 : Formula for counting the number of times "0" appears in consecutive cells in one row

Any help would be greatly appreciated....

I'm trying to create an excel spreadsheet to help us manage a small dance school which we run.

Basically we are trying to track student attendance...

I'd like to know what formula to use if I want to find out when a student has not attended for 3 consecutive weeks (an absent day is represented by the number 0, attendance is represented by the number 1). This way we can send them a message to make sure everything is OK. Additionally, I'd like to be able to find if a student has not attended for 10 consecutive weeks (so we can remove them from our database).

Currently, Column A has all the names.

Column B is the intended column where I would like to put the formula.

Column C counts how many times the student has attended class (represented by the number 1)

Column D onwards is for each week of class. The cell on the row with the student's name on it gets a "1" if they attended and a "0" if they were absent.

Thankyou in advance for your help!!

Hello and welcome,

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

Thanks for taking the time to read and comply with the rule .

Something like this:

=SUM(IF(FREQUENCY(IF(D2:N2=0,COLUMN(D2:N2)),IF(D2:N2=1,COLUMN(D2:N2)))=3,1))

Adjust ranges to suit (D2:N2) week entries in D2:N2...

formula to be confirmed with CTRL+SHIFT+ENTER not just ENTER

This tells you have many times 3 consecutive 0's show up...

for 10.. change the 3 in the formula above to 10.

Note: To return a message as long as 1 string of 3 consec/or 10 consec weeks missed, then use formula like:

=IF(SUM(IF(FREQUENCY(IF(D2:N2=0,COLUMN(D2:N2)),IF(D2:N2=1,COLUMN(D2:N2)))=3,1)),"3 Consecutive Weeks Missed","")

confirmed with CTRL+SHIFT+ENTER and copied down.