Using Named Ranges in a Summation inside of INDIRECT Function

New Contributor

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.  




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:



@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.