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?


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.

  • 1
    And to be clear, the output of the ADDRESS() function is a string (i.e., text), not a regular range reference that SUM() can take as an argument. – Excellll Jul 26 '12 at 15:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.