Forum Discussion
Using Named Ranges in a Summation inside of INDIRECT Function
Trying to use a named range inside of an INDIRECT Function that is being used to sum numbers in a range that has a dynamic number of entries. I am unable to use a named range for E82. I tried "E82_" as a named range but the formula returns #VALUE.
=(SUM(E82:INDIRECT("E"&"82"+COUNT(E83:E116))
2 questions,
1. Is there a way to get this to work?
2. Is there an easier way to do this? Do I need to use the INDIRECT Function?
3 Replies
ncallie301 I suspect you need to remove the quotation marks around the second "82". Try this in stead:
=(SUM(E82:INDIRECT("E"&82+COUNT(E83:E116))))Riny_van_Eekelen Thank you. I'm sorry I was not clear on the problem statement. I wanted to assign a name to the Cell E82, but upon further review, I do not think that is possible in this case.
ncallie301 Not sure what you are trying to do but this part of the formula,
"E"&82+COUNT(E83:E116) ,
will create a text, for example, E90, if the range E83:E116 contains 8 numbers.
The entire formula will then evaluate to =SUM(E82:E90).
If that is not your intention you can perhaps clarify.
