SUMPRODUCT ranges
Posted by Chris on November 23, 2001 6:06 AM
Using SUMPRODUCT, I can't ask it to just look at a column (ie A:A), it seems to always need a specific range (ie A1:A999) within the formula
is this right ? it's a pain having to put in the whole range rather than just being able to click on the column...
many thanks
Posted by Bob Umlas on November 23, 2001 7:27 AM
When you click on the column, you're requesting a formula to evaluate 65536 cells which is just too much for a SumProduct formula. Unfortunately, you do need to enter the address OR a range name.
Posted by Aladin Akyurek on November 23, 2001 7:40 AM
As Bob states, you can't use args like x!A:A with array or SUMPRODUCT formulas. You can however use:
[1] (dynamic) range names
[2] a UDF that computes the range in use dynamically, which enables formulas like =SUMPRODUCT((Used(A:A)="x")*(Used(B:B)=25)).
The second method requires that the formulas must be entered in the same worksheet where the ranges of interest are.
Aladin
=======
