ADDRESS function nested in SUM returns Err: 502
The ADDRESS()
function does just that. It provides a reference to a cell and not the contents of the cell. The INDIRECT()
function will take the cell references and provide the cell contents so the SUM()
function works properly.
Related videos on Youtube
Chuck
Updated on September 18, 2022Comments
-
Chuck over 1 year
When working on one problem, I came upon something that confuses me and was hoping for some clarification.
I am trying to use the
SUM()
function in a very round about way.The formula
=SUM(ADDRESS(ROW(blahblah), COLUMN(blahblah)) : ADDRESS(RoW(blahblah), COLUMN(blahblah)))
throws an Err:502
When I apply the
INDIRECT()
to theADDRESS()
s it works great.=SUM(INDIRECT(ADDRESS(ROW(blahblah), COLUMN(blahblah)) : INDIRECT(ADDRESS(ROW(blahblah), COLUMN(blahblah)))
returns a value that checks out.
What confuses me is that using when pulling the nested function apart the
ADDRESS()
functions will give a valid cell reference (EX:$B$4
) but the when added to the guts of aSUM()
theSUM()
pukes up an error code. So why would there need to be an additionalINDIRECT()
to make anADDRESS()
work when nested in aSUM()
?Any thoughts at all?
-
Excellll almost 12 yearsAnd to be clear, the output of the
ADDRESS()
function is a string (i.e., text), not a regular range reference thatSUM()
can take as an argument.