Indirect range in SUMPRODUCT?
 

If I have a formula that defines arange in cell A1
="column"&"row"&":"&"column"&"row"
and another range defined in the same way in B1,
I can reference them with
=SUMPRODUCT(INDIRECT(A1),INDIRECT(B1))

However I can not insert the formula in A1 instead of the
reference to A1 in the SUMPRODUCT(INDIRECT()) function. Is
there a way force this?

I ask because SUM, COUNTIF, and others will accept the
range definition instead of the reference to the range.

Of course this won't save me much space, just want it in a
couple places to make speadsheets a little neater.
Thanks

Hi!

Indirect will not accept arguments that evaluate as
formulas. It will only accept arguments that evaluate as
references.

Biff

-----Original Message-----
If I have a formula that defines arange in cell A1
="column"&"row"&":"&"column"&"row"
and another range defined in the same way in B1,
I can reference them with
=SUMPRODUCT(INDIRECT(A1),INDIRECT(B1))

However I can not insert the formula in A1 instead of the
reference to A1 in the SUMPRODUCT(INDIRECT()) function.
Is
there a way force this?

I ask because SUM, COUNTIF, and others will accept the
range definition instead of the reference to the range.

Of course this won't save me much space, just want it in
a
couple places to make speadsheets a little neater.
Thanks
.

Here is why I'm confused.
If I have data in column A
Then type
"a" in B1
1 in B2
10 in B3
=b1&c1&":"&b1&b3 in b4
I CAN use either
=sum(indirect(b4))
or
sum(indirect(b1&c1&":"&b1&b3))
this also works either way in several other formulas.

I'll assume you're referring to the SUMPRODUCT
manipulation of INDIRECT (and any others I find that don't
work ;)
Thanks



-----Original Message-----
Hi!

Indirect will not accept arguments that evaluate as
formulas. It will only accept arguments that evaluate as
references.

Biff

-----Original Message-----
If I have a formula that defines arange in cell A1
="column"&"row"&":"&"column"&"row"
and another range defined in the same way in B1,
I can reference them with
=SUMPRODUCT(INDIRECT(A1),INDIRECT(B1))

However I can not insert the formula in A1 instead of
the
reference to A1 in the SUMPRODUCT(INDIRECT()) function.
Is
there a way force this?

I ask because SUM, COUNTIF, and others will accept the
range definition instead of the reference to the range.

Of course this won't save me much space, just want it in
a
couple places to make speadsheets a little neater.
Thanks
.

.