2011 jan 1 2 3 4 5 feb 1 2 3 4
Place x in correct column
a x 0 x 0 x x x
b x 0 x x 0 0 x
c 0 0 0 x x 0 x
d x 0 x 0 x x x
e x 0 0 x 0 0 x
f 0 x x 0 x 0 x
g 0 x 0 0 0 0 0
h 0 x x x x 0 x


The above indicates members and their attendance.
Assuming this is the 2nd week of Feb, member b has missed 3 consecutive weeks.
Member h has missed 2.
Member g has missed 5.

I need to generate a report showing those that are 3 or greater.
My members will total in excess of 500.
I need a formula to calculate the current consecutive absences and the counter needs to restart on each individual that shows up on the current week as being present.
If they don't show up then add 1 more to the counter.
If H is present, then counter goes back to 0.
If G misses the counter goes to 6.
x=present
0=missed
I prefer using the actual Sunday date instead of 1,2,3,4,5.
I prefer using blank instead of 0.
tarquinious solution last works however I want to use this on a 52 week calendar. The calendar will have several consecutive blank cells however I only want to count from the current week backwards. consecutively

I need to implement this program asap if some one could help. thanks

I think I've grasped the concept of what you're after. Some of the finer points you may wish to re-explain, or see if you can modify the suggestions below to fit.

NB: I have taken into account your wish to use empty cells instead of 0's in your data collection, so the formula and function below use "" to check for absences.

For the Total Number of Absenses, use the formula:
=COUNTIF(B2:H2,"")
...where B2:H2 is the attendance records for a member. Copy this formula down for each member.

For your example above, this will result in the members scoring the following for Total Absences:
a=2
b=3
c=4
d=2
e=4
f=3
g=6
h=2
_________________________________

Now for the tricky one. Counting the number of consecutive absences requires a bit of VBA code. So in VisualBasic you will need to create a new module and paste in the following code:

Function CountConsec(UserRange)
NumConsecs = 0
MaxConsecs = 0
For Each c In UserRange 'Loop through each of the values
If c.Value = "" Then NumConsecs = NumConsecs + 1 Else NumConsecs = 0
If NumConsecs MaxConsecs Then MaxConsecs = NumConsecs
Next
CountConsec = MaxConsecs
End Function

Back in your worksheet, next to each row of data you can now use the following formula:
=CountConsec(B2:H2)
...where B2:H2 is the attendance records for a member. Copy this formula down for each member.

This results in the following number of consecutive absences for the members in your example above:
a=1
b=2
c=3
d=1
e=2
f=1
g=5
h=1

I re-read your request and I may have mis-interpreted what you were after. I have had another stab at it and come up with the following function.

What I think you're looking for is a formula to only count consecutive absences if they are absent in the current (i.e. latest) week.

As before, copy and paste this code into VBA (if you need help doing this, let me know and I can post detailed instructions) and then in your worksheet, next to each row of data you can use the formula:
=LatestAbsences(A2:H2)
...where A2:H2 is the row of data you want to check for the latest absences in. Unfortunately this function can only work on one row at a time, and you will need to adjust the formula each time you add a week of data. Also, if you select more cells than you have captured data in, the function will think those empty cells are absences and count them too.

This gave me the following results based on your example above:
a=0
b=0
c=0
d=0
e=0
f=0
g=5
h=0

Function LatestAbsences(UserSelection)
If UserSelection.Item(UserSelection.Count) = "" Then 'If the last item in the selection is blank
AttendenceRecordNum = UserSelection.Count 'Start at the end
Do Until UserSelection.Item(AttendenceRecordNum) < "" 'Loop until a non-blank is found
AbsenceCount = AbsenceCount + 1 'Keep adding consecutive absences
AttendenceRecordNum = AttendenceRecordNum - 1 'Step back 1 record
Loop
LatestAbsences = AbsenceCount 'Put the absence count back in the worksheet
End If
End Function

Given a bit more time I could probably knock up something a little more robust than this, but I hope this covers your needs in the interim.

Any queries/problems, let me know.