Excel SumProduct function is great for multiple criteria
Free Microsoft Office Tutorials
The Excel SumProduct function enables you to sum multiple criteria. In some ways it is even more flexible than the Sumifs that were introduced with Microsoft 2007. Why? Because the SumProduct function allows for AND as well as OR.
I’m going to show you how Excel SumProduct function works that I’m going to show you what you can get SumProduct to do and finally will have a look at exactly what makes SumProduct work.
This information applies to Microsoft Excel 2003/2007/2010
Sample workbook for Excel SumProduct function.
Please subscribe to Online PC Learning before downloading
Harness the Power of the Excel SumProduct function Video1
Harness the Power of the Excel SumProduct function Video 2
Basically SumProduct multiplies adjacent value is in a range. In this illustration you can see that we have three ranges Amount/ Quantity /Frequency. SumProduct would multiply 30 x 2 x 1 in the first row then multiply 20 x 8 x 1 in the second row then multiply 121 x 3 x 2 and so on. The result would be 1688 as you can see in the illustration. This is what SumProduct was designed for.(Click illustration to show in lightbox)
This is what it is designed for
You can also use excel SumProduct function to work with specific criteria in equal ranges. In the illustration below we are using to text criteria from the header Name and the header Month. Because the SumProduct function works on true or false which is 1 or 0 we need to convert the text and numbers. To do this we use a double minus sign called a double unary. You could also use the multiplication simple “*” if more than one value is involved. This formula searches through the two ranges and sums the values in the third range when the criteria in the first two ranges are met. You can have as many criteria as you want. I have only used 2 here.(Click illustration to show in lightbox)
This what you can do with the function
Here is a trick that I often use on worksheets with SumProduct to add some flexibility. Simply create to named ranges for the criteria ranges. In this instance we would create a named range for all of the data under Name and another named range for all of the values in the Month range. Where we formally referenced the text value (eg "Brenda") in the formula we would now just simply replace that with a cell reference( eg G30. You will be able to see this in the illustration below. This will add a dynamic aspect to the SumProduct function. Now you do not need multiple formulas. This turns the Excel SumProduct function into a search by criteria. (Click illustration to show in lightbox)
Used with data validation lists
Exactly how does the SumProduct function work? I found this very interesting and I hope I can explain to you so that you understand the concept here. When you understand this writing your formulas will become much easier. I mentioned earlier that the SumProduct function works with true or false which is 1 or 0.
If you look at the illustration below you will see that I have on the left changed the result to true or false and on the right I have referenced this by 1 and 0.(Click illustration to show in lightbox)
If we look at the first row you will see that we have T and T which equals (1 x 1) x 3500 which equals 3500.
Shows T and F which equals (1 x 0) x 4000 which equals 0. Watch the second video to see this more clearly. Download the workbook and test it out.
How it works
This is what makes the SumProduct function in many ways even more powerful than the SumIfs function. The SumIfs function will only allow for the use of AND which we have used here in this illustration with the SumProduct function.
It is also possible with the SumProduct function to use the + sign which translates to an OR. This is not possible with the SumIfs function which only allows for multiple AND criteria.(Click illustration to show in lightbox)
Online PC Learning is committed to providing free office tutorials