The SUMPRODUCT() function...

Introduction:

The SUMPRODUCT() function is a very important function when it comes to validiate multiple conditions and sum a certain range that satifies the given criteria. In this post i will explain and highlight some of the very commonly encountered situtation where SUMPORDUCT() function can be used.

The Syntex:

The MS Excel 2007 describes it as:

SUMPRODUCT(array1,array2,array3, ...)

Array1, array2, array3, ...   are 2 to 255 arrays whose components you want to multiply and then add.

With Remarks that:
1. The array arguments must have the same dimensions. If they do not, SUMPRODUCT returns the #VALUE! error value. 
2. SUMPRODUCT treats array entries that are not numeric as if they were zeros

Senario # 01: 

The basic usage of SUMPRODUCT() is when we try to multiple different ranges of equal diemntions and want to get their sum. The attached sample file has tab Senario # 01 that describes the process so it will easy if you download it:

Product Price Quaintity

A 10 50
B 15 55
C 20 40
D 25 35

For the given example that consitute the Range from A1:C5, we enter following formula in Cell C6 to get the PRODUCT+SUM of Price & Quaintity:

=SUMPRODUCT($B$2:$B$5,$C$2:$C$5)   

If you select the cell C6 and go to Tab Formula > Evaluate Formula it will take you from follwing steps:

Step 01: =SUMPRODUCT($B$2:$B$5,$C$2:$C$5)   
Step 02: =SUMPRODUCT((10,15,20,25,),(50,55,40,35))
Step 03: =SUMPRODUCT((500,825,800,875))
Step 04: =3000

Thus an additional column that had been need to multiply the Price and Quaintity has been avoided and we get the same result.

Senario # 02:

The SUMPRODUCT() function can be used to verifiy multiple conditions. Contining with the same example of Product, Price & Quaintity, we add some further details to elaborate this type of usage as well. Please switch to the second sheet named "Senario # 02" to understand this example:

Product Month Price Quaintity
A Jan, 12 10 50
B Feb, 12 15 55
C Mar, 12 20 40
A Jan, 12 25 35
B Feb, 12 35 15
B Mar, 12 40 20
C Apr, 12 45 27

First Situation: Summing the Quaintity for a particular month:

In such a case we will setup a cell to input the "Month" for which we want to sum the sales. The formula in this case will look like:

=SUMPRODUCT(($B$2:$B$8=$C$11)*(D2:D8))

For this formula to work we will enter in $C$11 the desired month eg "Jan,12", the formula will work like this:

Step 01: =SUMPRODUCT(($B$2:$B$8=$C$11)*(D2:D8))
Step 02: =SUMPRODUCT(({"Jan, 12","Feb, 12","Mar, 12","Jan, 12","Feb, 12","Mar, 12","Apr, 12",=$C$11)*(D2:D8))
Step 03: =SUMPRODUCT(({"Jan, 12","Feb, 12","Mar, 12","Jan, 12","Feb, 12","Mar, 12","Apr, 12",="Jan, 12"}))*(D2:D8))
Step 04: =SUMPRODUCT({True,False,False,True,False,False,False}*{50,55,40,35,15,20,27})
Step 05: =SUMPRODUCT({50,False,False,35,False,False,False})
Step 06: =85

Second Sitatuion: If we want to get "Total Cost" then we will multiply it with ($C$2:$C$8), the formula will look like then

=SUMPRODUCT(($B$2:$B$8=$C$11)*($C$2:$C$8)*($D$2:$D$8))

Step 01: =SUMPRODUCT(($B$2:$B$8=$C$11)*(D2:D8))
Step 02: =SUMPRODUCT(({"Jan, 12","Feb, 12","Mar, 12","Jan, 12",...,"Apr, 12",=$C$11)*($C$2:$C$8)*(D2:D8))
Step 03: =SUMPRODUCT(({"Jan, 12","Feb, 12","Mar, 12","Jan, 12",...,"Apr, 12",="Jan, 12"}))*($C$2:$C$8)*(D2:D8))
Step 04: =SUMPRODUCT({True,False,False,True,...,False}*{10,15,20,25,35,40,45}*{50,55,40,35,15,20,27})
Step 05: =SUMPRODUCT({500,False,False,875,False,False,False})
Step 02: =1375   

Third Situation: Consider the thrid sheet where we have to check for Month as well as week, the Month is present in columns while week numbers are present as the header row of the table. We have incorporated an option in the workshee that will look into the range of the weeks specified through input cell C7:C8, the month is specified in C9. The table looks like this:

Product Month Price 1 2 3 4 5
A Jan, 12 10 40 10 30 30 50
B Feb, 12 15 50 20 10 30 20
C Mar, 12 20 40 10 30 10 20

And the formula is: 

=SUMPRODUCT(($B$3:$B$5=$C$9)*($C$3:$C$5)*($D$2:$H$2>=$C$7)*($D$2:$H$2<=$C$8)*($D$3:$H$5))

Step 01: =SUMPRODUCT({"Jan, 12","Feb, 12","Mar, 12"}="Jan, 12"*($C$3:$C$5)*($D$2:$H$2>=$C$7)*($D$2:$H$2<=$C$8)*($D$3:$H$5))
Step 02: =SUMPRODUCT({True,False,False}*{10,15,20}*{1,2,3,4,5>=1}*{1,2,3,4,5<=2}*($D$3:$H$5))
Step 03: =SUMPRODUCT({True,False,False}*{10,15,20}*{1,2,3,4,5>=1}*{1,2,3,4,5<=2}*{40,10,30,30,50,.....20,40,10,30,10,20})
Step 04: =SUMPRODUCT({10,0,0}*{1,2,3,4,5>=1}*{1,2,3,4,5<=2}*{40,10,30,30,50,.....20,40,10,30,10,20})
Step 05: =SUMPRODUCT({True,True,True,True,True}*{1,2,3,4,5<=2}*{40,10,30,30,50,.....20,40,10,30,10,20})
Step 06: =SUMPRODUCT({10,10,10,10,10,0,0,0,0,0,0,0,0,0,0}*{True,True,Flase,Flase,Flase}*{40,10,30,30,50,.....20,40,10,30,10,20})
Step 07: =SUMPRODUCT({10,10,0,0,0,0,0,0,0,0,0,0,0,0,0}*{40,10,30,30,50,.....20,40,10,30,10,20})
Step 08: =SUMPRODUCT({400,100,0,0,0,0,0,0,0,0,0,..,0,0,0,0})
Step 09: =500

Fourth Situtation:  We can use "<>" operator to select everything else then a particular criteria, see the fourth sheet for this example: 

The table looks like following:

Product Month Price Quaintity
A Jan, 12 10 50
B Feb, 12 15 55
C Mar, 12 20 40
A Jan, 12 25 35

Lets assume that we can to sum the Total Cost for every thing else then "A" we will replace the equal sign "=" with "<>":

=SUMPRODUCT(($A$2:$A$5<>"A")*($C$2:$C$5)*($D$2:$D$5))

The formula will check the first condition and will give these result: 

Step 01: =SUMPRODUCT({"A","B","C","A"<>"A"}*($C$2:$C$5)*($D$2:$D$5))
Step 02: =SUMPRODUCT({False,True,True,False}*{10,15,20,25}*{50,55,40,35})
Step 03: =SUMPRODUCT({0,825,800,0})
Step 04: =1625

Conclusion: The SUMPRODUCT() function gives you flexibiility to check for multiple criterias and sum a range, in caes of unique value, it can also retrive one that meets your critieria, but being an array formula it works slower then SUMIF().