Using Named Ranges in a Summation inside of INDIRECT Function

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

 

=(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.