# 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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,013
Messages
5,834,925
Members
430,326
Latest member
tomwax46

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