Thanks! You're a genius. :-)

For the consecutive 0s, i'd like to know if a formula is possible to
calculate the following (see table below):

A B C D E
0 W -0.5 2 2
1 W +0.5 2 2
0 D +0.5 2 1
0 W -0.5 1 2
0 D -0.5 1 2
1 W -0.5 2 1
0 W +0.5 1 1
1 D +0.5 1 2

When the condition of consecutive 0s (in this table instance is 3 in a row)
has been met, the cell has another condition to calculate the following:
i) taking into account the cell of column C immediately after the
consecutive 0s (i.e. C7 in sample table above), it needs to take (D7-E7)+C7
to equate with either a positive or negative result. Based on above table,

(D7-E7)+C7 is (2-1)+(-0.5) = +0.5 (positive result)

ii) another example would be if the roles are reversed for D7 and E7 where
D7 is 1 while E7 is 2. The result would be:

(D7-E7)+C7 is (1-2)+(-0.5) = -1.5 (negative result)

With the above 2 examples, the formula cell will then register a count (1)
if its a positive result, and zero/none (0) if its a negative result. Is this
possible?

Thanks again!

"T. Valko" wrote:

Try these array formulas** :

Count 2 consecutive Ws:

=SUM(--(FREQUENCY(IF(B2:B9="w",ROW(B2:B9)),IF(B2:B9<"w", ROW(B2:B9)))=2))

Count 3 consecutive 0s. Assumes no empty cells within the range. Empty cells
evaluate as 0 and could cause incorrect results. If there might be empty
cells the formula can be tweaked to account for them.

=SUM(--(FREQUENCY(IF(A2:A9=0,ROW(A2:A9)),IF(A2:A9<0,ROW( A2:A9)))=3))

Note that these formulas are explicit in how they count.

W
W
W
W

That would not be counted as 2 instances of 2 consecutive Ws. That is
considered 1 instance of 4 consecutive Ws.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"andrew" wrote in message
...
I have the following table:

A B
0 W
1 W
0 D
0 W
0 D
1 W
0 W
1 D

Is there a way to count or sum up a series of text or number within a
column? E.g.
i) count the number of times the alphabet W appears in sequence of 2 times
(i.e. B2 and B3 in a column)? Table above shows W appearing twice in
sequence.
ii) count the number of times the number zero (0) appears in a sequence of
3
times within a column?

Appreciate any help, thanks!