SUM returns #VALUE! error
 

In B6 the formula
=SUM(ADDRESS(B2,COLUMN(B6))&":"&ADDRESS(B3,COLUMN( B6)))
returns a #VALUE! error. The formula
=SUM(INDIRECT(B5))
returns the correct answer, when B5 contains
=ADDRESS(B2,COLUMN(B6))&":"&ADDRESS(B3,COLUMN(B6))
and returns the correct absolute address $B$9:$B$25, a range with some
numbers.
B2&B3 values may vary and the sum fomula is to be filled across row 6.

Am I missing something obvious?

Kevin

SUM returns #VALUE! error
 

Hi

You need to wrap it in an indirect function itself for Sum to return the
answer.

=SUM(INDIRECT((ADDRESS(B2,COLUMN(B6))&":"&ADDRESS( B3,COLUMN(B6)))))

But you would have fewer calls it you did it directly (well indirectly
really<bg) with

=SUM(INDIRECT("B"&B2&":B"&B3)) note the colon before the second B

--
Regards

Roger Govier


"kjs" wrote in message
...
In B6 the formula
=SUM(ADDRESS(B2,COLUMN(B6))&":"&ADDRESS(B3,COLUMN( B6)))
returns a #VALUE! error. The formula
=SUM(INDIRECT(B5))
returns the correct answer, when B5 contains
=ADDRESS(B2,COLUMN(B6))&":"&ADDRESS(B3,COLUMN(B6))
and returns the correct absolute address $B$9:$B$25, a range with some
numbers.
B2&B3 values may vary and the sum fomula is to be filled across row 6.

Am I missing something obvious?

Kevin

SUM returns #VALUE! error
 

Hi

You need to wrap it in an indirect function itself for Sum to return the
answer.

=SUM(INDIRECT((ADDRESS(B2,COLUMN(B6))&":"&ADDRESS( B3,COLUMN(B6)))))

But you would have fewer calls it you did it directly (well indirectly
really<bg) with

=SUM(INDIRECT("B"&B2&":B"&B3)) note the colon before the second B

--
Regards

Roger Govier

Thanks for the fix Roger. I'll be using the top one as I need to fill across
columns.

Kevin