ADDRESS function nested in SUM returns Err: 502

Question

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 the ADDRESS()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 a SUM() the SUM() pukes up an error code. So why would there need to be an additional INDIRECT() to make an ADDRESS() work when nested in a SUM()?

Any thoughts at all?

Answer 1

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.