Domenic,

I had a similar issue and just tried your solution. It worked perfect! I'm
trying to read through the function to try to understand what you actually
did. Haven't quite figured it out yet, but it works anyway!

Thanks again.

"Domenic" wrote:

Maybe...

=IF(COUNTIF(B1:BA1,"P"),COUNTIF(INDEX(B1:BA1,MATCH (2,1/(B1:BA1="P"))):BA1
,"A"),COUNTIF(B1:BA1,"A"))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Adjust the range
accordingly.

Hope this helps!

In article ,
"jerry37917" wrote:

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