SUM returns #VALUE! error

[kjs]

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

 

[Roger Govier]

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

 

[kjs]

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

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

Kevin