Dynamic sum of a range

[misterxo]

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

 

[oldbrewer]

		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]

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

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

 

[Canapone]

Hi All,


another approach:

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


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

 

[misterxo]

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

 

[MARK858]

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.