| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif-sumproduct
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumif-sumproduct
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 On Sep 12, 9:16 am, yshridhar wrote: 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumif-sumproduct
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 -- Regards Roger Govier "yshridhar" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumif-sumproduct
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 =107 and =--(107) and see the difference. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "yshridhar" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumif-sumproduct
Thanks to all
What is the resulatant speed when i make them array by incorporating another condition? Sridhar "Bob Phillips" wrote: 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 =107 and =--(107) and see the difference. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "yshridhar" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumif-sumproduct
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) "yshridhar" wrote in message ... Thanks to all What is the resulatant speed when i make them array by incorporating another condition? Sridhar "Bob Phillips" wrote: 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 =107 and =--(107) and see the difference. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "yshridhar" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumif-sumproduct
Thanks Mr. Bob for your clarification and Thanks to all
Regards Sridhar "Bob Phillips" wrote: 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) "yshridhar" wrote in message ... Thanks to all What is the resulatant speed when i make them array by incorporating another condition? Sridhar "Bob Phillips" wrote: 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 =107 and =--(107) and see the difference. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "yshridhar" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
| HELP!!!! sumif or sumproduct ??? :( | Excel Worksheet Functions | |||
| HELP!!!! sumif or sumproduct ??? :( | Excel Worksheet Functions | |||
| SUMIF/SUMPRODUCT?? | Excel Worksheet Functions | |||
| SUMIF or SUMPRODUCT or something else? | Excel Worksheet Functions | |||
Linear Mode
