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!

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!

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!

For the consecutive 0s, i'd like to know
if a formula is possible to...

Good grief!!!!

My head is spinning on that one! I have no idea how to do that in a single.
It could be done using multiple helper formulas but there's a problem with
your requirement.

taking into account the cell of column C
immediately after the consecutive 0s

What if the cells are like this:

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

There is no cell immediately after the consecutive 0s.

???

--
Biff
Microsoft Excel MVP


"andrew" wrote in message
...
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!

If the cells stops at the end of the consecutive 0s, then the formula cell
will return a 0 or nil. Is this possible?

Lets try using a simpler table:
A B C D E
1 W -0.5 2 2
0 W +0.5 2 2
0 D +0.5 2 1
0 W -0.5 1 2
1 D -0.5 1 0

Using the above table, an independent formula cell will check the following
conditions:
1) three (3) consecutive 0s are met in column A, then if match;
2) checks the following row (row 5 for above) if cell C5 is negative digit,
then if match;
3) it takes (D5-E5)+C5 - i.e. in example above is (1-0)-0.5 = +0.5;
4) if total is + (positive) then it registers in formula cell as 1. If total
is - (negative) it registers as 0 (zero).

Can SUMPRODUCT with different array formulas be used for the above?



"T. Valko" wrote:

For the consecutive 0s, i'd like to know
if a formula is possible to...

Good grief!!!!

My head is spinning on that one! I have no idea how to do that in a single.
It could be done using multiple helper formulas but there's a problem with
your requirement.

taking into account the cell of column C
immediately after the consecutive 0s

What if the cells are like this:

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

There is no cell immediately after the consecutive 0s.

???

--
Biff
Microsoft Excel MVP


"andrew" wrote in message
...
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!

Anyone? Please don't give up...i'm sure someone out there can help me figure
this out...thanks a million in advance.

"andrew" wrote:

If the cells stops at the end of the consecutive 0s, then the formula cell
will return a 0 or nil. Is this possible?

Lets try using a simpler table:
A B C D E
1 W -0.5 2 2
0 W +0.5 2 2
0 D +0.5 2 1
0 W -0.5 1 2
1 D -0.5 1 0

Using the above table, an independent formula cell will check the following
conditions:
1) three (3) consecutive 0s are met in column A, then if match;
2) checks the following row (row 5 for above) if cell C5 is negative digit,
then if match;
3) it takes (D5-E5)+C5 - i.e. in example above is (1-0)-0.5 = +0.5;
4) if total is + (positive) then it registers in formula cell as 1. If total
is - (negative) it registers as 0 (zero).

Can SUMPRODUCT with different array formulas be used for the above?



"T. Valko" wrote:

For the consecutive 0s, i'd like to know
if a formula is possible to...

Good grief!!!!

My head is spinning on that one! I have no idea how to do that in a single.
It could be done using multiple helper formulas but there's a problem with
your requirement.

taking into account the cell of column C
immediately after the consecutive 0s

What if the cells are like this:

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

There is no cell immediately after the consecutive 0s.

???

--
Biff
Microsoft Excel MVP


"andrew" wrote in message
...
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!