Sum cells based on values in a different row

[broncosrul]

I am trying to sum a couple ranges based on labels in another row. The reason that I need it to be based on these labels is that the range will change. I have managed to get cell addresses in a very clunky way, but I can't get them to sum. Plus, it seems like there is probably a better way to identify the range. (just now thinking that I may be able to do this with SUMIF). But anyway, here is where I am at.

Row 1 is text and cannot be formatted differently. I was able to get some of the addresses with these formulas. Cell B2 would always be consistant. Basically, I need to sum the 2016 range and the 2017 range.

=ADDRESS(2,MATCH(TRUE,INDEX(RIGHT(A4:I4,2)="2017",0),0),4,1)

delivered E2

=ADDRESS(2,MATCH(TRUE,INDEX(A1:R1="",0),0)-2,4,1)

delivered G2

But I don't know how to sum those delivered addresses.
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Wk
[/TD]
[TD]W/E 07/02/2016
[/TD]
[TD]W/E 07/09/2016
[/TD]
[TD]W/E 07/16/2016
[/TD]
[TD]W/E 07/01/2017
[/TD]
[TD]W/E 07/08/2017
[/TD]
[TD]W/E 07/15/2017
[/TD]
[TD]YOY $ Chg
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Sales
[/TD]
[TD]$500
[/TD]
[TD]$800
[/TD]
[TD]$300
[/TD]
[TD]$750
[/TD]
[TD]$500
[/TD]
[TD]$900
[/TD]
[TD]need formula for this cell that would deliver =(sum(E2:G2)-sum
(B2:D2))
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[broncosrul]

I was able to get an answer using

=(SUMPRODUCT(--(RIGHT($A$1:$G$1,4)="2017"),A2:G2)-SUMPRODUCT(--(RIGHT($A$1:$G$1,4)="2016"),A2:G2))

But I would still like some other options or at least figure out how to sum a range using the cell addresses that I extracted.

 

[Fluff]

With your first formula in J2 & your second in K2 try this in L2
=SUM(INDIRECT(J2):INDIRECT(K2))