View previous topic :: View next topic 
Author 
Message 
JLeite General User
Joined: 04 Jan 2011 Posts: 15 Location: Porto, Portugal

Posted: Sat Jan 15, 2011 2:33 pm Post subject: (Solved) Formulas differences 


I normally use the following formula for conditional sum:
{=SUM(IF($D$5:$D$20=G9;$E$5:$E$20;""))}
instead of a more traditional:
=SUMIF($D$5:$D$20;G9;$E$5:$E$20)
The result seems to be the same in both cases and the first one seems more natural to me.
My doubts are:
Are they really identical?
If so, in very large spreadsheets where there are hundreds or thousands of rows and dozens of columns, is there any advantage in any of them in terms of calculation speed or used memory?
Best regards
JLeite
P.S. Here's a attachment with an example
http://www.mediafire.com/?5fit9dcs0u39pjz
Last edited by JLeite on Fri Jan 28, 2011 8:22 am; edited 1 time in total 

Back to top 


ken johnson Super User
Joined: 23 Apr 2009 Posts: 2032 Location: Sydney, Australia

Posted: Sat Jan 15, 2011 3:56 pm Post subject: 


SUMIF is faster than {=SUM(IF(...))}
To illustrate the performance difference I used Code:  =RANDBETWEEN(1;100)  to fill D1:D65535 with random integers from 1 to 100 then used Copy/Paste Special to convert the formulas to there number values.
Then I just put 1 into E1 and doubleclicked its fill handle to fill E1:E65535 with 1,2,3,....65535.
Next I used Code:  =RANDBETWEEN(1;100)  and Copy/Paste Special to fill G1:G500 with random integers from 1 to 100.
After that I entered the formula Code:  =SUMIF($D$1:$D$65535;G1;$E$1:$E$65535)  into H1 then doubledclicked its fill handle so that it would be filled down to H500.
During the calculation the mouse pointer changes to a spinning blue wheel and it went on for 7 revolutions before the calculations were completed.
I then backspaced out the SUMIF formulas in H1:H500 and array entered Code:  =SUM(IF($D$1:$D$65535=G1;$E$1:$E$65535;""))  into H1.
This time, after I held down the Ctrl key and doubleclicked H1's fill handle, the spinning blue wheel went on for 55 revolutions before calculation of the array formulas in H1:H500 were completed.
Array formulas should only be used when the same task is not possible with standard functions.
Ken Johnson _________________ If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button). 

Back to top 


JLeite General User
Joined: 04 Jan 2011 Posts: 15 Location: Porto, Portugal

Posted: Fri Jan 28, 2011 9:01 am Post subject: 


ken johnson wrote:  SUMIF is faster than {=SUM(IF(...))}
To illustrate the performance difference I used Code:  =RANDBETWEEN(1;100)  to fill D1:D65535 with random integers from 1 to 100 then used Copy/Paste Special to convert the formulas to there number values.
Then I just put 1 into E1 and doubleclicked its fill handle to fill E1:E65535 with 1,2,3,....65535.
Next I used Code:  =RANDBETWEEN(1;100)  and Copy/Paste Special to fill G1:G500 with random integers from 1 to 100.
After that I entered the formula Code:  =SUMIF($D$1:$D$65535;G1;$E$1:$E$65535)  into H1 then doubledclicked its fill handle so that it would be filled down to H500.
During the calculation the mouse pointer changes to a spinning blue wheel and it went on for 7 revolutions before the calculations were completed.
I then backspaced out the SUMIF formulas in H1:H500 and array entered Code:  =SUM(IF($D$1:$D$65535=G1;$E$1:$E$65535;""))  into H1.
This time, after I held down the Ctrl key and doubleclicked H1's fill handle, the spinning blue wheel went on for 55 revolutions before calculation of the array formulas in H1:H500 were completed.
Array formulas should only be used when the same task is not possible with standard functions.
Ken Johnson 
Thank you Ken.
After your explanation I've changed some of my spreadsheets. But I still have a problem.
Many times I need to make a conditional summation where I do several checks for example:
http://www.mediafire.com/download.php?4badprmegjbhy58
In Sheet2, cell B5 has a typical example of what I mean. In this cases how sumif works?
Best regards
JLeite 

Back to top 


ken johnson Super User
Joined: 23 Apr 2009 Posts: 2032 Location: Sydney, Australia

Posted: Fri Jan 28, 2011 1:51 pm Post subject: 


SUMIF can only handle one condition.
SUMPRODUCT can handle up to 29 conditions when used to sum a column of values according to the criteria.
Try...
Code:  =SUMPRODUCT(Sheet1.F2:F1300=B1;Sheet1.G2:G1300=B2;Sheet1.H2:H1300=B3;Sheet1.I2:I1300=B4;Sheet1.J2:J1300) 
SUMPRODUCT is a standard function, however, its performance can be slow because it works like an array function.
Ken Johnson _________________ If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button). 

Back to top 


JLeite General User
Joined: 04 Jan 2011 Posts: 15 Location: Porto, Portugal

Posted: Fri Jan 28, 2011 3:49 pm Post subject: 


ken johnson wrote:  SUMIF can only handle one condition.
SUMPRODUCT can handle up to 29 conditions when used to sum a column of values according to the criteria.
Try...
Code:  =SUMPRODUCT(Sheet1.F2:F1300=B1;Sheet1.G2:G1300=B2;Sheet1.H2:H1300=B3;Sheet1.I2:I1300=B4;Sheet1.J2:J1300) 
SUMPRODUCT is a standard function, however, its performance can be slow because it works like an array function.
Ken Johnson 
I think I got it.
As SUMPRODUCT only has a vector, it only sums and doesn't multiply anything, right?
JLeite 

Back to top 


ken johnson Super User
Joined: 23 Apr 2009 Posts: 2032 Location: Sydney, Australia

Posted: Fri Jan 28, 2011 7:57 pm Post subject: 


Each parameter in the SUMPRODUCT function can be considered to be a vector.
For example, say there are 3 parameters, each having five vector components...
SUMPRODUCT({a1a2a3a4a5};{b1b2b3b4b5};{c1c2c3c4c5})
SUMPRODUCT calculates the sum of the products of the components...
a1*b1*c1+a2*b2*c2+a3*b3*c3+a4*b4*c4+a5*b5*c5
When the components of two of the three parameters are the results of tests for criteria, there values will be either TRUE (1) or FALSE (0) and the SUMPRODUCT result will be the sum of the third parameter's components that correspond to both criteria being TRUE.
Example...
SUMPRODUCT({10101};{11001};{c1c2c3c4c5})
=1*1*c1+0*1*c2+1*0*c3+0*0*c4+1*1*c5
=c1+0+0+0+c5
=c1+c5
Ken Johnson 

Back to top 


JLeite General User
Joined: 04 Jan 2011 Posts: 15 Location: Porto, Portugal

Posted: Sat Jan 29, 2011 12:22 pm Post subject: 


ken johnson wrote:  Each parameter in the SUMPRODUCT function can be considered to be a vector.
For example, say there are 3 parameters, each having five vector components...
SUMPRODUCT({a1a2a3a4a5};{b1b2b3b4b5};{c1c2c3c4c5})
SUMPRODUCT calculates the sum of the products of the components...
a1*b1*c1+a2*b2*c2+a3*b3*c3+a4*b4*c4+a5*b5*c5
When the components of two of the three parameters are the results of tests for criteria, there values will be either TRUE (1) or FALSE (0) and the SUMPRODUCT result will be the sum of the third parameter's components that correspond to both criteria being TRUE.
Example...
SUMPRODUCT({10101};{11001};{c1c2c3c4c5})
=1*1*c1+0*1*c2+1*0*c3+0*0*c4+1*1*c5
=c1+0+0+0+c5
=c1+c5
Ken Johnson 
If I understand, when we compare a column with a value we also have a vector with zeros when it's false and ones when it's true, and the SUMPRODUCT makes the scalar product between vectors. Am I getting it?
JLeite 

Back to top 


ken johnson Super User
Joined: 23 Apr 2009 Posts: 2032 Location: Sydney, Australia

Posted: Sat Jan 29, 2011 1:46 pm Post subject: 


JLeite wrote:  If I understand, when we compare a column with a value we also have a vector with zeros when it's false and ones when it's true, and the SUMPRODUCT makes the scalar product between vectors. Am I getting it?
JLeite 
Sounds perfect!
Ken Johnson 

Back to top 


JLeite General User
Joined: 04 Jan 2011 Posts: 15 Location: Porto, Portugal

Posted: Sat Jan 29, 2011 2:47 pm Post subject: 


ken johnson wrote:  JLeite wrote:  If I understand, when we compare a column with a value we also have a vector with zeros when it's false and ones when it's true, and the SUMPRODUCT makes the scalar product between vectors. Am I getting it?
JLeite 
Sounds perfect!
Ken Johnson 
Ok. Thank you, Ken.
JLeite 

Back to top 


