Sum range of columns by Row (Percent Rent)

[MichelleC987]

Hi All,

I'm hoping for some ideas on a problem that has puzzled me for years. I have a number of cost centers that need to have sales summarized by different time frames (lease years). For example, cost center 00010 may have a lease year of February - January; the sales for each month are organized in a matrix style, cost center (rows), months (columns).

Ideally, looking to have the 'Lease Year Sales' calculated as a dependent function of the cost center and the lease year. Any help would be greatly appreciated! Thank you!!

Master Sales Worksheet
[TABLE="width: 873"]
<colgroup><col><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]Store Number[/TD]
[TD]January 2017[/TD]
[TD]February 2017[/TD]
[TD]March 2017[/TD]
[TD]April 2017[/TD]
[TD]May 2017[/TD]
[/TR]
[TR]
[TD]00010[/TD]
[TD] 9,619.31[/TD]
[TD] 41,916.31[/TD]
[TD] 48,844.89[/TD]
[TD] 98,188.39[/TD]
[TD] 91,889.40[/TD]
[/TR]
[TR]
[TD]00011[/TD]
[TD] 19,481.18[/TD]
[TD] 41,139.60[/TD]
[TD] 41,849.68[/TD]
[TD] 36,199.38[/TD]
[TD] 38,998.90[/TD]
[/TR]
[TR]
[TD]00021[/TD]
[TD] 8,344.18[/TD]
[TD] 6,139.89[/TD]
[TD] 16,348.98[/TD]
[TD] 18,894.98[/TD]
[TD] 1,999.31
[/TD]
[/TR]
</tbody>[/TABLE]

Desired Calc
[TABLE="width: 442"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Cost Center[/TD]
[TD]Lease Year[/TD]
[TD]Lease Year Sales[/TD]
[/TR]
[TR]
[TD]00010[/TD]
[TD]January 2017 - May 2017[/TD]
[TD] 290,458.30[/TD]
[/TR]
[TR]
[TD]00011[/TD]
[TD]April 2017 - March 2018[/TD]
[TD] 75,198.28[/TD]
[/TR]
[TR]
[TD]00021[/TD]
[TD]February 2017 - April 2017[/TD]
[TD] 41,383.85[/TD]
[/TR]
</tbody>[/TABLE]

 

[kvsrinivasamurthy]

Origoinal data is A1:F4
Below data is from A10:D13
A B C D E
Cost Center Lease Year from ​ Lease Year to Lease Year Sales Expected
00010 Jan-2017 May-2017 2,90,458.30 2,90,458.30
00011 Apr-17 Mar-18 75,198.28 75,198.28
00021 Feb-17 Apr-17 41,383.85 41,383.85

Formula in D11 then drag down

=SUM(INDEX(INDEX($B$2:$F$4,MATCH($A11,$A$2:$A$4,0),),MATCH($B11,$B$1:$F$1,1)):INDEX(INDEX($B$2:$F$4,MATCH($A11,$A$2:$A$4,0),),MATCH($C11,$B$1:$F$1,1)))

 

[kvsrinivasamurthy]

All dates in my above reply should be in same format.

 

[MichelleC987]

This is fantastic! Index, Match is my go-to for items with only two dependents, didn't realize it had this much potential. Thank you so much kvsrinivasamurthy, everyone in the office is thrilled with your resolution!!

 

[kvsrinivasamurthy]

Thanks for the feedback