Aladin,
Would you comment on the perceived advantage of using
+0
Instead of the asterisk or the unary.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Aladin Akyurek" wrote in message
...
One way is to reduce the number of ranges tested...

Example

Suppose we have

[A]

=SUMPRODUCT(($A$2:$A$1000=$K2)+0,($B$2:$B$1000=$L2 )+0,$C$2:$C$1000)

and we want to reduce the number of ranges tested:

D2, copied down:

=A2&"#"&B2

The formula in [A] can be re-expressed as:

[b]

=SUMIF(($D$2:$D$1000,$K2&"#"&$L2,$C$2:$C$1000)

For more ideas, see:

http://tinyurl.com/d9eom

For computing on relevant subranges instead of whole range, the
following example might be helpful:

http://tinyurl.com/cqy47

And for more, see:

http://tinyurl.com/d9eom

Mike wrote:
I have a workbook that uses over 3000 sumproduct formulas to extract
data based on 7 different criteria. Works great except for calculation
time.

Is there an alternative to Sumproduct that would be faster and still
allow for sumation based on multiple criteria?

Is it possible to have Excel calculate certain cell ranges but not the
entire workbook?

Would additional RAM help? (I currently have 1 MB)

Is there a way to identify things that make calc time longer?

Thanks for any suggestions...


--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.