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]

 

[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...


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

0​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

3​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

4​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

3​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

3​

[/td][td="bgcolor:#D9D9D9"][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

5​

[/td][td="bgcolor:#D9D9D9"]

18​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

3​

[/td][td="bgcolor:#D9D9D9"]

21​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

0​

[/td][td="bgcolor:#D9D9D9"][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

5​

[/td][td="bgcolor:#D9D9D9"][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

4​

[/td][td="bgcolor:#D9D9D9"][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td]

7​

[/td][td="bgcolor:#D9D9D9"][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

12​

[/td][td]

3​

[/td][td="bgcolor:#D9D9D9"][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

13​

[/td][td]

5​

[/td][td="bgcolor:#D9D9D9"]

24​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

14​

[/td][td]

4​

[/td][td="bgcolor:#D9D9D9"]

28​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

15​

[/td][td][/td][td][/td][/tr]
[/table]

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..


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

0​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

3​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

4​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

3​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

3​

[/td][td="bgcolor:#D9D9D9"][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

5​

[/td][td="bgcolor:#D9D9D9"][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

3​

[/td][td="bgcolor:#D9D9D9"]

21​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

0​

[/td][td="bgcolor:#D9D9D9"][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

1​

[/td][td="bgcolor:#D9D9D9"][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

4​

[/td][td="bgcolor:#D9D9D9"][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td]

7​

[/td][td="bgcolor:#D9D9D9"][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

12​

[/td][td]

3​

[/td][td="bgcolor:#D9D9D9"][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

13​

[/td][td]

5​

[/td][td="bgcolor:#D9D9D9"][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

14​

[/td][td]

4​

[/td][td="bgcolor:#D9D9D9"]

23​

[/td][/tr]
[/table]

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.