Counting highest group of letters

  • Thread starter Thread starter Steve Jackson
  • Start date Start date
S

Steve Jackson

I have a single column of data with just 3 different letters A, B and
C. I am trying to find out the highest number of times that A appears
consequtively after each other.

e.g. If the column of letters was "A A A B C B A B A C A C C B" then
the highest number of times that A ran consequtively would be 3 (these
were the first 3 occurances of A).

I am able to count the total occurances of a single letter using the
COUNTIF function but can't think of a way to counting groups of data
and also find the highest. Is there a function in Excel that will
complete this?
 
I don't know of a single function that will do it but how about this:

If your column of letters starts in A2 then in B2 put

=IF(A2="A",+B1+1,0)

Copy this down to the end of the column. Then in B1 use

=max(b2:b1000) (or however long the column is
 
Back
Top