sumif-sumproduct

[Guest]

Hello everybody
Which is more advantegious sumif or sumproduct in terms of execution speed?
the following are my formulae both are giving same result.
=SUMif(SCHOOL!$J$1:$J$950,$B$1,SCHOOL!$V$1:$V$950)
=SUMPRODUCT(--(SCHOOL!$J$1:$J$950=$B$1),(SCHOOL!$V$1:$V$950))
My second question:
What is the significance of "--" in the formula? When i remove it the
formula results in error. Can it be use with other formulae?
Thanks for all
Sridhar

 

[Pete_UK]

SUMIF is faster, particularly with larger arrays.

The -- converts the True/False values to 1's and 0's to be used in the
SUMPRODUCT formula, and yes, it can be used in other formulae to
convert Boolean values to numeric.

Hope this helps.

Pete

 

[Roger Govier]

Hi

I would expect the SUMIF to be faster.

The use of the double unary minus -- is to coerce the result of the test
SCHOOL!$J$1:$J$950=$B$1
from True False to 1 and 0, so they can be multiplied by the values in
V1:V950.
The results of that, either the value from column V where J = B1, or 0 where
the value in J does not equal B1, are then summed to produce your result.

For more help on this topic read the excellent documents by
Bob Phillips at
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

or by JE McGimpsey at
http://www.mcgimpsey.com/excel/formulae/doubleneg.html

 

[Bob Phillips]

SUMIF is much quicker, SUMPRODUCT whilst not being an array function acts
like one, and suffers the overheads of that.

See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
explanation. If you remove it it won't work as explained. And yes it can be
used anywhere, such as

Try

=10>7

and

=--(10>7)

and see the difference.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Thanks to all
What is the resulatant speed when i make them array by incorporating another
condition?
Sridhar

 

[Bob Phillips]

SP tends to be marginally faster than say

=SUM(IF(...

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Thanks Mr. Bob for your clarification and Thanks to all
Regards
Sridhar