Summing a range built from two addresses

[ajlauten]

Hello,

I want to sum a range built by two separate addresses (that will change based on user input), I have the formulas to return the correct address but cant seem to get a sum without an error.

Address 1: =ADDRESS(4,MATCH(WaterFalls!C4,'Calcs&fixed data for plots'!A2:BE2,0)) => $E$4
Address 2: =ADDRESS(4,MATCH(WaterFalls!C5,'Calcs&fixed data for plots'!A2:BE2,0)) => $G$4

How would I get to =SUM($E$4:$G:$4)
I also will want to get to =AVERAGE($E$4:$G:$4)

 

[Joe4]

Let's say that you have your formula for Address 1 in cell A1, and the formula for Address 2 in cell B1.
Then you should be able to do something like this:

=SUM(INDIRECT(A1 & ":" & B1))
=AVERAGE(INDIRECT(A1 & ":" & B1))

 

[Fluff]

Another option is

=SUM(INDEX('Calcs&fixed data for plots'!D5:BE5,MATCH(C4,'Calcs&fixed data for plots'!D2:BE2,0)):INDEX('Calcs&fixed data for plots'!D5:BE5,MATCH(C5,'Calcs&fixed data for plots'!D2:BE2,0)))

Also you should never refer to the name of the sheet the formula is on.