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.

	A	B	C	D	E	F	G	H	I
1	Wk	W/E 07/02/2016	W/E 07/09/2016	W/E 07/16/2016	W/E 07/01/2017	W/E 07/08/2017	W/E 07/15/2017	YOY $ Chg
2	Sales	$500	$800	$300	$750	$500	$900	need formula for this cell that would deliver =(sum(E2:G2)-sum (B2:D2))

<tbody>
</tbody>

 

[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))