Thank you very much. Your code wasn't exactly what I was looking for, but it
gave me what I needed. I was able to modify it slighly and achieve the count
that I was wanting. I am relatively new to Excel and didn't even know this
was possible. Wow! I didn't realize just how powerful Excel really is.
Thanks again.

Jerry

"Bob Phillips" wrote:

Easily achieved with VBA. Assuming that the dates start in column C and the
absence count is in column B

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
Dim nCount As Long
Dim nMax As Long

On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Column 2 Then
With Target
For i = 3 To Me.Cells(1, Columns.Count).End(xlToLeft).Column
If Cells(.Row, i).Value = "a" Then
nCount = nCount + 1
Else
If nCount nMax Then
nMax = nCount
End If
nCount = 0
End If
Next i
Me.Cells(.Row, "B").Value = nMax
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.





--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"jerry37917" wrote in message
...
I have a worksheet that lists all the members of our church. Across the
top
are all the Sundays in the year. Next to each person's name, I am filling
the sheet with either an A (Absent) or P (present). To the far left, I
have
a column called "number of consecutive absences". What I need is a
formula
to count the number of consecutive absences from the most reason Sunday.
Something like this:

John Smith A P A A P A A A
3

In the example John has missed three consecutive Sundays. If he is
present
at the next meeting, the number would reset to 0. If he is absent, it
would
then be 4. I don't need to count the total number of absences, just the
number of absences since the last attendance.

The purpose is to help our outreach teams keep in contact. When a person
is
absent, we want to send a card, but when a person is absent twice in a row
we
want to make a call...three times...a visit...and so on. This worksheet
will
make it a lot easier to keep track of what the teams should be doing.

Thanks in advance for your help.

Jerry