sumif-sumproduct

Y

yshridhar

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
 
P

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
 
R

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
 
B

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)
 
Y

yshridhar

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

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top