Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .