Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 6 down vote accepted

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.

share|improve this answer
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
Thanks. I learned something new. – Chuck Aug 1 '12 at 20:13

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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