Hi Harlan, now i'm getting a bit confused. :-)
Anyway, perhaps lets try with a simpler version:

S
A
G
S
S
A
G
S
S
S
A

Assuming the text are all in 1 column (and the row keeps increasing weekly),
i would like to have a formula cell that calculates with the following
criterias:
1) FORMULA #1
if "S" appears =2 times in sequence (i.e. one after another), register
TRUE. In the above table, "S" appeared =2 times on 2 (two) occasions (row
A4&A5, then row A8-A10). The condition is to capture the most recent sequence
(i.e. row A8-A10) and display the result as (number of times it appeared in
latest sequence), which is three (3). Does this make sense?

2) FORMULA #2
If above is possible, then is it also possible to track two different text
which are in sequence (using the same condition as above)? Using above column
example, the text "S" and "A" together appeared in sequence three (3) times
(1st sequence from row A1-A2, then A4-A6, then A8-A11) - with the latest
showing a total of 4 (A8-A11) using the most recent sequence as a result in
the formula cell.

I hope it doesn't sound too confusing but i know you're near to getting it
perfect so don't give up pls.. :-)

"Harlan Grove" wrote:

andrew wrote...
Hi there. I have a column consisting of the following:

S A S S S G S A A S S S S A G A S S A S

I need to track the aphabet "S" if it appears =2 times. The column cell is
updated weekly. In the above sequence of the column (left to right), "S"
appeared 3 times first then 4 times again after the break in sequence. [see
bracket e.g. S A (S S S) G S A A (S S S S) A G A S S A S]

You're missing a grouping:

S A (S S S) G S A A (S S S S) A G A (S S) A S

The formula for the cell to track and sum up should base on the condition
that if "S" =2 then start counting until break in sequence. If it appears 3
times first, then a break, then 4 times again in sequence - the formula cell
should register the latest sequence count (i.e. 4 times). Is this possible?

Would the latest sequence count for the sample data above really be 4,
or would it be 2?

If you want length of the last sequence of S's in the range D of
length greater than or equal to N, try the array formula

=LOOKUP(2,1/(MMULT(((MMULT(-(ROW(D)=TRANSPOSE(ROW(D))),-(D<"S"))
=TRANSPOSE(MMULT(-(ROW(D)=TRANSPOSE(ROW(D))),-(D<"S")))))*(D="S"),--
(D="S"))=N),
MMULT(((MMULT(-(ROW(D)=TRANSPOSE(ROW(D))),-(D<"S"))
=TRANSPOSE(MMULT(-(ROW(D)=TRANSPOSE(ROW(D))),-(D<"S")))))*(D="S"),--
(D="S")))