Summing a range using INDIRECT & ADDRESS

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to sum a range on the "Data" sheet. I will actually be using the
MATCH function within the ADDRESS function to ultimatly identify the range to
sum. But I cannot even get this fuction to work. It gives the #Ref! error.


=SUM(INDIRECT("Data!"&(ADDRESS(55,5))&":"&(ADDRESS(55,64))))

Thanks for any help.
Todd
 
Your formula works for me, so long as I have a sheet named Data. You have a
few needless sets of (), but they don't interfere with the formula.

=SUM(INDIRECT("Data!"&ADDRESS(55,5)&":"&ADDRESS(55,64)))
 
Vasant Nanavati wrote...
Works for me. Do you have a sheet called Data?
Click to expand...

There's a not too unlikely chance the OP's worksheet name is more
complicated than Data. If it includes spaces, then it needs to be
delimited by single quotes. Good idea always to delimit the worksheet
name with single quotes.

=SUM(INDIRECT("'Data'!"&...))

No good reason to use INDIRECT or ADDRESS. If the first cell is fixed
and the second to be given by a MATCH call, then better to use

=SUM('Data'!$E$55:INDEX('Data'!$E$55:$IV$55,MATCH(...)))

which replaces the volatile INDIRECT call with a nonvolatile INDEX call.
 
Back
Top