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>

 

[DRSteele]

I think this might help. I know it's not exactly what you sought, but I can't figure out a way to prevent the display of the streak when there is a continuing streak below it, as in Row6-7.

Book1
	A	B
1	0
2	3
3	4
4	3
5	3
6	5	18
7	3	21
8	0
9	1
10	4
11	7
12	3
13	5
14	4	23
Sheet14

Cell Formulas
Range	Formula
B1	=IFERROR(SUM(OFFSET(A1,,,-IF((COUNTA($A$1:A1)-MATCH(1,INDEX(1/($A$1:A1<2),0)))>4,COUNTA($A$1:A1)-MATCH(1,INDEX(1/($A$1:A1<2),0)),0))),"")

 

[Ron Coderre]

With your sample data in A1:A14, this regular formula (copied down) seems to do what you want:

B1: =IFERROR(IF(AND(COUNTIF(A1:INDEX(A$1:A1,LOOKUP(2,1/(A$1:A1<=2)
,ROW(A$1:A1))+1),">2")>=5,A2<=2)
,SUM(A1:INDEX(A$1:A1,LOOKUP(2,1/(A$1:A1<=2),ROW(A$1:A1))+1)),""),"")

Is that something you can work with?

 

[jtakw]

Hi,

I added a bit to DRSteele's formula in Post #2 to "prevent" showing of the "streak" result, don't know if it's a good idea, credit goes to DRSteele for the original formula:

Book1
	A	B
1	0
2	3
3	4
4	3
5	3
6	5	18
7	3
8	0
9	1
10	4
11	7
12	3
13	5
14	4	23
Sheet120

Cell Formulas
Range	Formula
B1	=IFERROR(IF(OFFSET(B1,-1,0)="",SUM(OFFSET(A1,,,-IF((COUNTA($A$1:A1)-MATCH(1,INDEX(1/($A$1:A1<2),0)))>4,COUNTA($A$1:A1)-MATCH(1,INDEX(1/($A$1:A1<2),0)),0))),""),"")

Don't know if the SUM should be in Row 6 or 7, OP's description would say 6, but OP's sample shows 7

Edit: Nope, won't work, if the 0 (zero) in A8 was replaced by 2 or greater.

 

[DRSteele]

Ah, I see how to do my formula to account for continuing streaks. Thanks Ron.

=IFERROR(SUM(OFFSET(A1,,,-IF(AND(A2<=2,(COUNTA($A$1:A1)-MATCH(1,INDEX(1/($A$1:A1<2),0)))>4),COUNTA($A$1:A1)-MATCH(1,INDEX(1/($A$1:A1<2),0)),0))),"")

 

[Marcelo Branco]

Maybe...

	A​	B​
1​	0​
2​	3​
3​	4​
4​	3​
5​	3​
6​	5​	18​
7​	3​	21​
8​	0​
9​	5​
10​	4​
11​	7​
12​	3​
13​	5​	24​
14​	4​	28​
15​

Formula in B5 copied down (gray area)
=IF(COUNTIF(A1:A5,">2")=5,IF(ISNUMBER(B4),B4+A5,SUM(A1:A5)),"")

M.

 

[Ron Coderre]

Comment: I assumed that if more than 5 consecutive items was greater than 2...Add all of them.
I may be wrong (it wouldn't be the first time :| )

 

[Marcelo Branco]

I think i misunderstood what are the desired results, that is, to show the sum only at the end of the streak.

Maybe this new version..

	A​	B​
1​	0​
2​	3​
3​	4​
4​	3​
5​	3​
6​	5​
7​	3​	21​
8​	0​
9​	1​
10​	4​
11​	7​
12​	3​
13​	5​
14​	4​	23​

Formula in B5 copied down
=IF(AND(COUNTIF(A1:A5,">2")=5,A6<=2),SUM(A5:INDEX(A$1:A4,IFERROR(1+LOOKUP(2,1/(A$1:A4<=2),ROW(A$1:A4)-ROW(A$1)+1),1))),"")

M.

 

[Rick Rothstein]

It is hard to know what the OP wants given that one of the summed numbers in his original message is wrong. The 21 is the sum of all 6 numbers in the streak whereas the 23 is the sum of the last 5 numbers in the streak consisting of, again, 6 numbers. The way I read the text, I believe the OP only wants to sum the last 5 number in the streak no matter how long the streak is (which means the 21 should have been 18). If I am correct, I am pretty sure this somewhat simple, normally-entered formula placed in cell B5 and copied down should work...

B5: =IF(AND(A5>2,A6<=2,INDEX(A1:A5,ROW($1:$5))>2),SUM(A1:A5),"")

 

[Marcelo Branco]

It is hard to know what the OP wants given that one of the summed numbers in his original message is wrong. The 21 is the sum of all 6 numbers in the streak whereas the 23 is the sum of the last 5 numbers in the streak consisting of, again, 6 numbers.

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

M.