Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 . |
#3
|
|||
|
|||
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 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I reference =, <, or > sign in SUMPRODUCT | Excel Discussion (Misc queries) | |||
SUMPRODUCT with date range question | Excel Discussion (Misc queries) | |||
using sumproduct in a range of text fields? | Excel Worksheet Functions | |||
Sumproduct function not working | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |