Sum the max of consecutive numbers

V

voltrader

Board Regular
Joined
Dec 17, 2009
Messages
58
Office Version
  1. 2010
Platform
  1. Windows
Hi All I am trying to Sum the Max of consecutive numbers for cells greater equal than 2, for instance


[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]Consecutive Sum Max[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks!
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
Is there always going to be 4 columns? What answer would you want if the values are: 3 2 1 7?
 
Upvote 0
assuming there are no negative values

Code: 
=SUM(A2:D2)-COUNTIF(A2:D2,1)
 
Upvote 0
[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]
a​
[/TD]
[TD]
b​
[/TD]
[TD]
c​
[/TD]
[TD]
d​
[/TD]
[TD]
e​
[/TD]
[/TR]
[TR]
[TD]
1​
[/TD]
[TD]
0​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD]
3​
[/TD]
[TD]
7​
[/TD]
[/TR]
[TR]
[TD]
2​
[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
0​
[/TD]
[/TR]
[TR]
[TD]
3​
[/TD]
[TD]
3​
[/TD]
[TD]
2​
[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
5​
[/TD]
[/TR]
</tbody>[/TABLE]





E1=SUMIF(A1:D1,">=2") copy down
 
Upvote 0
Eric W said:
Is there always going to be 4 columns? What answer would you want if the values are: 3 2 1 7?
Click to expand...

Hi Eric, there can be more than 4 columns. if
[TABLE="width: 500"]
<tbody>[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]7[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I would like to see 7 as the result. it is the max.

Thanks!
 
Upvote 0
Thanks for the reply, this seems to work but it doesn't adjust for non consecutive cells. for instance it sums all the cells greater than 2. If there is a non successive cell I need the number to reset, and for the final result to be the greatest of the successive values.
 
Upvote 0
Quite a challenge! I gotta think there's an easier way, but this is what I came up with:

ABCDEFGH
ABCDConsecutive Sum Max

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]

</tbody>
Sheet8​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]H2[/TH]
[TD="align: left"]=maxconsecutive(A2:D2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]F2[/TH]
[TD="align: left"]{=AGGREGATE(14,6,SUBTOTAL(9,OFFSET(A2,0,SMALL(IF(COLUMN(A2:D2)=COLUMN(A2),0,IF(A2:D2>2,IF(SUBTOTAL(9,OFFSET(A2,0,COLUMN(A2:D2)-COLUMN(A2)-1))<2,COLUMN(A2:D2)-COLUMN(A2)))),COLUMN(A2:D2)-COLUMN(A2)+1),1,IF(COUNTIF(B2:D2,">=2")+1=COLUMNS(A2:D2),COLUMNS(A2:D2),TRANSPOSE(FREQUENCY(IF((A2:D2>=2)+(COLUMN(A2:D2)=COLUMN(A2)),COLUMN(A2:D2)),IF((A2:D2<2)*(COLUMN(A2:D2)<>COLUMN(A2)),COLUMN(A2:D2)))))))-IF((COLUMN(A2:D2)=COLUMN(A2))*(A2:D2<2),A2:D2,0),1)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



The formula in F2 is an array formula, confirm with Control+Shift+Enter. It should be adaptable to wider ranges, just change the ranges in the formula. Be careful though, the range in the COUNTIF starts at the second column.

What is probably a better idea is to use a User-Defined Function (UDF). The formula probably took me a couple hours to derive, the UDF about 3 minutes. To install it, open a copy of your workbook. Press Alt-F11 to open the VBA editor. From the menu, select Insert > Module. On the sheet that opens, paste this code:

Code: 
Public Function MaxConsecutive(ByVal target As Range)
Dim c As Range, CurMax As Double

    For Each c In target
        If c.Value < 2 Then
            MaxConsecutive = WorksheetFunction.Max(CurMax, MaxConsecutive)
            CurMax = 0
        Else
            CurMax = CurMax + c.Value
        End If
    Next c
    MaxConsecutive = WorksheetFunction.Max(CurMax, MaxConsecutive)
    
End Function
Press Alt-Q to close the editor. Now just enter the formula in H2. Much simpler!

Hope this helps.
 
Last edited:
Upvote 0
I tried the UDF it works great! I will stick with the UDF it seems a lot cleaner.

Thanks alot!

Eric W said:
Quite a challenge! I gotta think there's an easier way, but this is what I came up with:

ABCDEFGH
ABCDConsecutive Sum Max

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]

</tbody>
Sheet8​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]H2[/TH]
[TD="align: left"]=maxconsecutive(A2:D2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]F2[/TH]
[TD="align: left"]{=AGGREGATE(14,6,SUBTOTAL(9,OFFSET(A2,0,SMALL(IF(COLUMN(A2:D2)=COLUMN(A2),0,IF(A2:D2>2,IF(SUBTOTAL(9,OFFSET(A2,0,COLUMN(A2:D2)-COLUMN(A2)-1))<2,COLUMN(A2:D2)-COLUMN(A2)))),COLUMN(A2:D2)-COLUMN(A2)+1),1,IF(COUNTIF(B2:D2,">=2")+1=COLUMNS(A2:D2),COLUMNS(A2:D2),TRANSPOSE(FREQUENCY(IF((A2:D2>=2)+(COLUMN(A2:D2)=COLUMN(A2)),COLUMN(A2:D2)),IF((A2:D2<2)*(COLUMN(A2:D2)<>COLUMN(A2)),COLUMN(A2:D2)))))))-IF((COLUMN(A2:D2)=COLUMN(A2))*(A2:D2<2),A2:D2,0),1)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



The formula in F2 is an array formula, confirm with Control+Shift+Enter. It should be adaptable to wider ranges, just change the ranges in the formula. Be careful though, the range in the COUNTIF starts at the second column.

What is probably a better idea is to use a User-Defined Function (UDF). The formula probably took me a couple hours to derive, the UDF about 3 minutes. To install it, open a copy of your workbook. Press Alt-F11 to open the VBA editor. From the menu, select Insert > Module. On the sheet that opens, paste this code:

Code: 
Public Function MaxConsecutive(ByVal target As Range)
Dim c As Range, CurMax As Double

    For Each c In target
        If c.Value < 2 Then
            MaxConsecutive = WorksheetFunction.Max(CurMax, MaxConsecutive)
            CurMax = 0
        Else
            CurMax = CurMax + c.Value
        End If
    Next c
    MaxConsecutive = WorksheetFunction.Max(CurMax, MaxConsecutive)
    
End Function
Press Alt-Q to close the editor. Now just enter the formula in H2. Much simpler!

Hope this helps.
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Counting Consecutive Blanks
Replies
9
Views
708
felixstraube
F
C
  • Solved
Count of Consecutive Letters in a Row
Replies
3
Views
176
Peter_SSs
Peter_SSs
Y
  • Question Question
How to count number of consecutive wet days in the a rainfall table?
2
Replies
19
Views
841
felixstraube
F
O
  • Solved
Most Consecutive appearances in a range
2 3
Replies
27
Views
348
Fluff
Fluff
K
  • Question Question
Compress consecutive numbers
Replies
9
Views
387
Fuji
F

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top