How to sum variable cell range withing array?

hmm

I did it a different way....

	A	B	C	forumula for column C
17	Cummulate Income for selection
18	Year		2012
19	Number of Years to go back		0
20	Income by year's investment		267	=SUM(OFFSET(C2,0,MATCH($C$18,$C$1:$CN$1,0)-1):OFFSET(INDIRECT(ADDRESS(ROW()-18,MATCH($C$18,$C$1:$CN$1,0)+2)),0,-IF($C$19>MATCH($C$18,$C$1:$CN$1,0)+1,MATCH($C$18,$C$1:$CN$1,0),$C$19)))
21	Income by year's investment SFH		147	=SUM(OFFSET(C3,0,MATCH($C$18,$C$1:$CN$1,0)-1):OFFSET(INDIRECT(ADDRESS(ROW()-18,MATCH($C$18,$C$1:$CN$1,0)+2)),0,-IF($C$19>MATCH($C$18,$C$1:$CN$1,0)+1,MATCH($C$18,$C$1:$CN$1,0),$C$19)))
22	Income by year's investment MFH		78	=SUM(OFFSET(C4,0,MATCH($C$18,$C$1:$CN$1,0)-1):OFFSET(INDIRECT(ADDRESS(ROW()-18,MATCH($C$18,$C$1:$CN$1,0)+2)),0,-IF($C$19>MATCH($C$18,$C$1:$CN$1,0)+1,MATCH($C$18,$C$1:$CN$1,0),$C$19)))
23	Income by year's investment PnR		3	=SUM(OFFSET(C5,0,MATCH($C$18,$C$1:$CN$1,0)-1):OFFSET(INDIRECT(ADDRESS(ROW()-18,MATCH($C$18,$C$1:$CN$1,0)+2)),0,-IF($C$19>MATCH($C$18,$C$1:$CN$1,0)+1,MATCH($C$18,$C$1:$CN$1,0),$C$19)))
24	Income by year's investment - nPnR		39	'=SUM(OFFSET(C6,0,MATCH($C$18,$C$1:$CN$1,0)-1):OFFSET(INDIRECT(ADDRESS(ROW()-18,MATCH($C$18,$C$1:$CN$1,0)+2)),0,-IF($C$19>MATCH($C$18,$C$1:$CN$1,0)+1,MATCH($C$18,$C$1:$CN$1,0),$C$19)))

This method lets you enter a year into Cell C18
Enter how many years to go back in cell C19 (zero years to view only the selected year)
If you enter 10, it will actually be giving results of 11 years, because it's going back 10 years and also including the year you selected, so if you want exactly 10 years worth of values then you should enter 9

In the rows below it will give you the cumulative results

Just copy and paste from the table above the formula for cell C20, you can then just copy and paste it down on the sheet
There's no need to paste the formulas in other columns, it's giving you all the answers you want in just the one column

If this solution isn't any good to you at all, then let me know and I'll see if I can re-work it

In your example file you didn't have anywhere to enter variables such as how many years to go back, which is why I just came up with the method I did. It might be worth you changing the example file to show exactly how you want the results to be displayed

example - ThirtyTwo.xlsx

Sorry for the delay in responded back to your post, been away working

I'm pleased you managed to salvage something from the formula I made, seems like the new sheet you created is giving you exactly what you were looking for, very impressed with how you adjusted that lengthy formula to make it work for all 90 years worth of data

Just noticed something you could change in the formula you created

change - MATCH(C1 to MATCH(C$1

This would have allowed you to copy the formula down and across without having to make the manual adjustments you did on each row in column C before copying and pasting across all columns, $1 would keep that row number fixed when you copy it down




If your question is resolved, mark it SOLVED using the thread tools
Click on the star (Add Reputation) if you think someone helped you with the information supplied in their post