Suming consecutive numbers greater than a value

[dkm06]

Hi all, I m presented a problem that I can describe in this way: In A column of numbers, sum only where the formula registers 5 consecutive numbers that are greater than 2 and tabulate the sum of that sequence in column b next to the last counted number greater than 2 in that sequence, example of set up below. For example if there are only 4 consecutive numbers greater than 2 in a sequence, that sequence is ignored. Thank you for your help,

[TABLE="width: 152"]
<tbody>[TR]
[TD][TABLE="width: 250"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]23[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

Rick,

The way i understood:
21 is the sum of 6 numbers because in the first sequence all numbers are greater than 2. On the other hand, 23 is the sum of only 5 numbers because 1 is not greater than 2, so it should not be included in the sum.

In other words: the first sequence begins in A2 and has, in fact, 6 numbers greater than 2; the second sequence begins in A10 and has only 5 numbers.

Not sure i'm right

Hmm, you are correct... I forgot about the "greater than 2" when I looked at the OP's numbers. Still, the OP's adding the 5 consecutive number criteria did seem to make the overall objective less clear. Not sure I can rework my current formula to handle a possibly unlimited sequence... I'll have to think about it.

 

[dkm06]

Hi Ron,

Thank you for your reply, your formula worked perfectly indeed, although I still have to figure out the way the formula works . Thanks again