# Dynamic sum of a range

#### misterxo

##### New Member
Hi, before posting this question I looked for similar others in this forum but could not find any answer. In any case apologies to the entire community if this was already asked and answered to.
This is my question:
-I have a row named "REVENUE" showing correspondent revenue values in \$ from C3 to N3
-Above the REVENUE row there is another row named "MONTH" showing the correspondent months of the year from C2 to N2 (1 to 12)
-In cell C10 there is a value to be entered between 1 and 12 (correspondent month of the year)
-In cell C11 there is a value to be entered between 1 and 12 (correspondent month of the year)

Basically C10 and C11 determine the dynamic range of time I want to base my sum of revenue from (for instance 3 to 7 is meant to be the sum of revenue between month 3 and month 7).

How can I create a formula IN A SINGLE NEW CELL ONLY to make this work?

Thanks
Al

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### oldbrewer

##### Well-known Member
 1 2 3 4 5 6 7 8 9 10 11 12 44 53 62 71 80 89 98 107 116 125 134 143 3 1025 7 1025 is got by =OFFSET(\$B\$1,1,MATCH(A5,\$C\$1:\$N\$1,0)+0)+OFFSET(\$B\$1,1,MATCH(A5,\$C\$1:\$N\$1,0)+1)+OFFSET(\$B\$1,1,MATCH(A5,\$C\$1:\$N\$1,0)+2)+OFFSET(\$B\$1,1,MATCH(A5,\$C\$1:\$N\$1,0)+3)+OFFSET(\$B\$1,1,MATCH(A5,\$C\$1:\$N\$1,0)+4)+OFFSET(\$B\$1,1,MATCH(A5,\$C\$1:\$N\$1,0)+5)+OFFSET(\$B\$1,1,MATCH(A5,\$C\$1:\$N\$1,0)+6)+OFFSET(\$B\$1,1,MATCH(A5,\$C\$1:\$N\$1,0)+7)+OFFSET(\$B\$1,1,MATCH(A5,\$C\$1:\$N\$1,0)+8)+OFFSET(\$B\$1,1,MATCH(A5,\$C\$1:\$N\$1,0)+9)+OFFSET(\$B\$1,1,MATCH(A5,\$C\$1:\$N\$1,0)+10)+OFFSET(\$B\$1,1,MATCH(A5,\$C\$1:\$N\$1,0)+11) you need to now subtract the next 12 totals from month 7 using the same approach this time you will be matching A6 of course far easier to use an out of the way helper row

<colgroup><col width="64" span="55" style="width:48pt"> </colgroup><tbody>
</tbody>

#### MARK858

##### MrExcel MVP
Possibly if including 3 & 7 (if not remove the = signs in the formula)...

=SUMIFS(C3:N3,C2:N2,">="&C10,C2:N2,"<="&C11)

#### Canapone

##### Active Member
Hi All,

another approach:

=SUM(INDEX(C3:N3,C10):INDEX(C3:N3,C11))

You can switch values in C10:C11: same result.

Last edited:

#### misterxo

##### New Member
Wow! This is a great forum. Thanks all for your help!

#### MARK858

##### MrExcel MVP
another approach:

=SUM(INDEX(C3:N3,C10):INDEX(C3:N3,C11))

Just to note the above doesn't work if the month numbers are not in order (I'm sure they are but just in case) i.e. 1,2,3,4,5,6,7,8,9,10,11,12 would work but 1,2,9,4,3,6,7,5,8,10,11,12 wouldn't work.

Edit, it also wouldn't work if the months ever don't start from month 1 but the question does state that it does start at 1 so it is just a note for possible future use.

Last edited:

Replies
2
Views
507
Replies
22
Views
545
Replies
3
Views
287
Replies
2
Views
245
Replies
3
Views
660

1,190,916
Messages
5,983,572
Members
439,850
Latest member
suhailrocks786

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