# Locate consecutive sequence of values & return column header name

#### Simon_L

##### New Member
Hi All,

Background: I have used the following formula to calculate the highest sum of 3, 6 or 12 consecutive columns in my dataset
= SUMPRODUCT(MAX(B2:V2+C2:W2+D2:X2+E2:Y2+F2:Z2+G2:AA2)) # this example is for 6 consecutive columns and returns the result 15, See screen shot #1 below, under the column named Imax_6hr.

Question: I am wondering if I can find where those 6 values are in each row and then return the name of the column header for the last value in that set of 6? see screen shot #2. The result (column header name) in this example is 18.
The other way to get the result of 18 would be to a) locate the set of 6 consecutive values and b) count the number of cells between the last value (S2) back towards (B2) that would also return the result of 18.

FYI - The data is hourly rainfall data. The column header names are hours. The actual dataset has 96 columns x 1250 rows.

# screen shot 1

#screen shot 2

Appreciate any help!

Thanks,
Simon

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Replies
6
Views
193
Replies
0
Views
787
Replies
3
Views
78
Replies
3
Views
63
Replies
10
Views
580

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