星期四, 一月 12, 2012

Formula Forensics. No 007 – Sumproduct

from chandoo.org
Formula Forensics. No 007 – Sumproduct:

One of the most asked questions within the posts and Forums at Chandoo.org is “How Does Sumproduct work ?”.


Rahul recently asked for an example in Excels Sumproduct Formula post; Comment No. 55.


So today in Formula Forensics we will take a look at just that with a few worked examples.


Sumproduct


Excels help defines Sumproduct as:



So what are these arrays referring to:


An array in Excel can be :


A manual Array: {10;20;30}


A Range: A1:A3


A Named Range: MyRange1


Where MyRange1 is defined as a defined range in the Name Manager.


A Named Formula: MyRange2


Where MyRange2 is defined as a Formula returning a range in the Name Manager.



Lets look at each


You can follow along in the Example file on Sheet1



An Array


In C2 type: =SUMPRODUCT({10;20;30})


Excel will display 60, which is the Sum of the array elements =10+20+30


A Range


C7: =Sumproduct(C4:C6)


Excel displays 60, which is the Sum of the cells from the range C4:C6 =10+20+30


A Named Range


In the Name Manager or Name Box define a Named Range


MyRange1: =Sheet1!$C$4:$C$6


Then in C10 type:


C10: =Sumproduct(MyRange1)


Excel displays 60, which is the Sum of the range elements =10+20+30



A Named Formula


In the Name Manager define a Named Formula


MyRange2 =OFFSET(Sheet1!$C$3,1,0,3,1)


Then in C12 type:


C12: =Sumproduct(MyRange2)


Excel displays 60, which is the Sum of the range elements from cells C4:C6 =10+20+30



You may be asking why use Sumproduct when we can use a simple Sum to add up 3 numbers?


The answer is to show you what Sumproduct is doing, it is Adding up each Array element.



What about the “Product” part of Sumproduct ?


Remember back at the start where we saw the Definition of Sumproduct,


SUMPRODUCT(array1, [array2], [array3], …)


Only Array 1 is required, Array 2, Array 3 etc are optional, that’s what the square brackets [ ] mean.



Multiple Arrays


Goto Sheet 2 in the Example file:


We will look at a simple example using two arrays



The data consists of Sales data.


Often we want to know what the total sales are


We do this by adding a Sales column



Which multiplies the Qty and Price columns


And then Sum (Add) up this new column



Returning our Total Sales of 15,000



Now we can manually check the above as the numbers are simple eg: 100*20 = 2,000 etc


And we can sum up the Sales and see that we in fact had total sales of 15,000



Well this is exactly what Sumproduct is made to do:


In a Blank cell enter: =SUMPRODUCT(D4:D8,E4:E8)



Excel will return 15,000.


So what is Sumproduct doing?


Lets look inside and see what’s going on


In the Example File, Sheet2, H1 there is a copy of the data laid out as below



Note that our formula =SUMPRODUCT(D4:D8,E4:E8)


Has two Arrays


Array 1: D4:D8


Array 2: E4:E8


Note that each corresponding Array Element is multiplied together


100 x 20


20 x 200 etc


These are the products of the two Arrays


Finally the Products are Added together and the correct answer 15,000 is returned.


So Sumproduct is the Sum of the Products of the Arrays


Of course we can extend that to a large number of Arrays, columns in this case, if we wish.




Sumproduct with Logic


In the above two examples we saw that Sumproduct can Sum a single Array and can Sum the Product of two or more Arrays.


We can use that to our advantage and build logic into the arrays, allowing us to optionally include some array elements and leave out others.


How?


Sumproduct will always add up the product of all Arrays.


So by including an Array where the elements within the Array that we don’t want to Sum are Zero and the Elements within the array that we do want to Sum are 1 we can control what is included in the final Summation.


Goto our Example File on Sheet3


Lets say we only want to include the Sales from our Northern Region


One way to do this is to purely delete the other entries



But what if we could do that without altering our worksheet or there are thousands of rows of data?


This is where Sumproduct comes into its own.


What we need to do is add some logic to our equation, effectively doing:



Lets try it with Sumproduct


In Cell F12: type =SUMPRODUCT(D4:D8,E4:E8,{FALSE;TRUE;FALSE;FALSE;TRUE})


Excel displays a -


Excel doesn’t know what to do with the True/False and so converts them to 0


We can force excel to evaluate these as numbers by adding a simple “1*”


In F14: Type =SUMPRODUCT(D4:D8,E4:E8,1*{FALSE;TRUE;FALSE;FALSE;TRUE})


Excel now displays 5,000 the total sales from the North


To see what has happened in F16 type: 1*{FALSE;TRUE;FALSE;FALSE;TRUE}, but don’t press Enter press F9 instead.


Excel displays ={0;1;0;0;1}


The use of the 1* has converted each of the Array elements from a True/False to a 1,0 respectively.


So our 3 arrays are now:



Now adding an Array of 1*{FALSE;TRUE;FALSE;FALSE;TRUE} every time we wanted to add some numbers isn’t a practical solution.


Excel has the ability to work construct an Array on our behalf!


In E18: enter =SUMPRODUCT(D4:D8,E4:E8,1*(C4:C8=”North”))


Excel will display 5,000


So 1*(C4:C8=”North”) is exactly equal to our previous array 1*{FALSE;TRUE;FALSE;FALSE;TRUE}


1*(C4:C8=”North”) = 1*{FALSE;TRUE;FALSE;FALSE;TRUE}


At the heart of this is that Excel is evaluating each cell in the Range: C4:C8 against our required logic =”North” and setting up an Array for us internally.


Simplify


The power of Sumproduct is therefore in that we can now simplify and extend


In cell E20 type: North


In cell F20 type: =SUMPRODUCT(D4:D8,E4:E8,1*(C4:C8=E20))


Excel will display 5,000


This simple addition allows us to vary the Summation based on the value in E20


We don’t need to multiply our logic array by 1, we can actually use any number or another Array.


In cell F22 type: =SUMPRODUCT(D4:D8,(E4:E8)*(C4:C8=E20))


This works as (C4:C8=E20) is returning an Array of True/False which get converted to an array of 1/0’s when subject to any maths.


The Math in this case is the multiplication by the 2nd Array (E4:E8)*(C4:C8=E20)



In Cell F24 type: =SUMPRODUCT(Qty, Price *(Region=SalesRegion))


Excel will display 5,000


But notice that by using Named Ranges/Formula how simple the logic of the equation has now become.



Rahul’s Question (Multiple Criteria):


In Comment No. 55: Rahul asked, “Can you give an example work sheet of above example


Sheet 4 in the Example File is the answer.



In Cell C23: type: =SUMPRODUCT(- -(A2:A21=”Luke Skywalker”),- -(B2:B21=”West”),C2:C21)


Excel will display 141, which is the sum of the Sales made by Luke Skywalker in the West Region.


However using what was learned above, this is better simplified to:


C26: =SUMPRODUCT((Name=SalesMan)*(Region=SalesRegion)*Sales)





The Double Unary


In the formula above Chandoo has used what is known as a Double Unary, which is 2 – signs next to each other (I have inserted a space above to make it more legible).


Two – signs are the same as saying


- -(A2:A21=”Luke Skywalker”) = -1 x -1 x (A2:A21=”Luke Skywalker”)


-1 x -1 is 1


Technically this is the most efficient way for Excel to perform any maths on the Array


- -(A2:A21=”Luke Skywalker”)


So that the Array of true/Falses made by (A2:A21=”Luke Skywalker”) is converted to an Array of 1/0’s for use in Sumproduct.


At the slight expense of speed but for improved readability and understandability by others I prefer the use of 1* instead of - – and you will mostly see that convention in my posts.


Chandoo: - -(A2:A21=”Luke Skywalker”)


Hui: 1*(A2:A21=”Luke Skywalker”)


In fact any maths performed on the array will convert its contents to an array of 1/0’s, so long as the maths doesn’t change the Arrays values


For a real good discussion on this topic have a look at the post The Venerable SUMPRODUCT at ExcelHero.com



Other Links to Sumproduct


http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/


http://chandoo.org/wp/2011/05/26/advanced-sumproduct-queries/


http://chandoo.org/wp/tag/sumproduct/


http://www.excelhero.com/blog/2010/01/the-venerable-sumproduct.html



DOWNLOAD


You can download a copy of the above file and follow along, Download Here.



OTHER POSTS IN THIS SERIES


You can learn more about how to pull Excel Formulas apart and what makes them tick in the following post:


Formula Forensic Series:



FORMULA FORENSICS NEEDS YOUR HELP !


I am running out of ideas for Formula Forensics and so I need your help.


If you have a neat formula that you would like to share and explain, try putting pen to paper and draft up a Post as Luke did in Formula Forensics 003. or like above.


If you have a formula that you would like explained but don’t want to write a post also send it in to Chandoo or Hui.



XMAS BREAK


This will be the last Formula Forensics Post for 2011, but rest assured that we will be returning in early 2012.


I’d like to take the opportunity to thank Chandoo for allowing me the space and freedom to post pretty much what ever I’ve wanted at Chandoo.org. I hope you have enjoyed my contributions to the Chandoo.org community over the past year.


On behalf of Eva and myself I’d like to wish you all a very Merry Xmas and a Happy and Safe New Year ahead


Hui…


Merry Xmas





没有评论: