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

Store Number	January 2017	February 2017	March 2017	April 2017	May 2017
00010	9,619.31	41,916.31	48,844.89	98,188.39	91,889.40
00011	19,481.18	41,139.60	41,849.68	36,199.38	38,998.90
00021	8,344.18	6,139.89	16,348.98	18,894.98	1,999.31

<colgroup><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>

Desired Calc

Cost Center	Lease Year	Lease Year Sales
00010	January 2017 - May 2017	290,458.30
00011	April 2017 - March 2018	75,198.28
00021	February 2017 - April 2017	41,383.85

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[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