# Finding the sum of the two highest consecutive values in a range

#### msoff

##### New Member
Hello,

I have a list of weeks with values associated to them as found in the excel picture attached.
I'm trying to find a formula to find the sum of the two highest values from 2 consecutive weeks.
Same thing with 10 consecutive weeks.

Ex:
Row1: Week 1, Week 2, Week 3
Row2: 5, 10, 15
In this example the answer would be 25.

#### Attachments

• Capture.JPG
18.1 KB · Views: 15

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

##### Well-known Member
Welcome to MrExcelMessage Board.
Try This with CTRL+SHIFT+ENTER if your Data is in A2:K2
Excel Formula:
``=MAX(A2:K2+B2:L2)``

NewRun.xlsm
ABCDEFGHIJKL
1Week1Week2Week3Week4Week5Week6Week7Week8Week9Week10Week11
2400813812124812
3
424
5
Sheet1
Cell Formulas
RangeFormula
G4G4=MAX(A2:K2+B2:L2)
Press CTRL+SHIFT+ENTER to enter array formulas.

#### jasonb75

##### Well-known Member
Allowing for the variable number of consecutive cells, this is the only method that comes to mind. You will need to make a few alterations to the relative references, etc depending on exactly how you want to set up the final sheet.
Book1
ABCDEFGHIJ
1Week 1Week 2Week 3Week 4Week 5Week 6Week 7Week 8Week 9Week 10
20012044812120
3
4WeeksResult
5224
6332
7436
81052
912Not enough data
Sheet1
Cell Formulas
RangeFormula
B5:B9B5=IFERROR(MAX(SUBTOTAL(9,OFFSET(A\$2,0,COLUMN(\$A\$2:INDEX(\$A\$2:\$J\$2,1,MATCH(1E+100,\$A\$2:\$J\$2)-\$A5)),1,\$A5))),"Not enough data")

#### Eric W

##### MrExcel MVP
Maybe:

Book1
ABCDEFGHIJ
1Week 1Week 2Week 3Week 4Week 5Week 6Week 7Week 8Week 9Week 10
20012044812120
3
424
Sheet2
Cell Formulas
RangeFormula
A4A4=AGGREGATE(14,6,A2:I2+B2:J2,1)

Replies
1
Views
862
Replies
2
Views
165
Replies
0
Views
274
Replies
14
Views
234
Replies
4
Views
501

### Forum statistics

1,127,107
Messages
5,622,782
Members
415,927
Latest member
vedasinternational

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

### Which adblocker are you using?

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

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