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,

A B 0 3 4 3 3 5 3 21 0 1 4 7 3 5 4 23 <tbody> </tbody>

<tbody>
</tbody>

 

[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