How to use the SUMPRODUCT Formula in Excel
The The SUMPRODUCT formula returns the sum of the products of corresponding ranges. The default operation is multiplication, but addition, subtraction, and division are also possible.
The SUMPRODUCT formula was used more frequently in versions of Microsoft Excel prior to 2007 before the SUMIFS formula was introduced. However, it still has benefit in its flexibility compared to SUMIFS as we will see in this lesson.
Explanation of the SUMPRODUCT formula:
=SUMPRODUCT(array1, [array2], [array3], …)
Array1 (required) – The first array argument whose components you want to multiply and then add.
[array2], [array3],… (optional) – Array arguments 2 to 255 whose components you want to multiply and then add.
Example #1 Using the SUMPRODUCT Formula:
The General Manager of a second-hand car dealership wants to know the sales for a certain product at each branch location for the current year.
The Financial Analyst is able to export from its Sales Journal (see below) the raw data with sale price, location, description and quantity sold.
The Financial Analyst can use the SUMPRODUCT formula to calculate the sales revenue by branch location and product description.
Solution:
To begin using the SUMPRODUCT formula, you have to remember that you’re creating arrays and making them equal to a specific criterion and you’re multiplying the different arrays by one another within the formula.
The total number of sales for each product description based on their respective branch is summarized below:
Sales Journal Raw Data:
*Note this table is only a sample of an example with 5 data entries while the rest of the data has been cut out for display purposes.
As shown in the example, we first highlighted the range of the car dealer branches and equated them to Alabama. Then we multiplied that with the product description column and equaling that to the quarter panel. Lastly, we multiplied them both with the sales revenue values for each product.
Example #2 Using the SUMPRODUCT Formula:
The General Manager of the second-hand car dealership wants to know the exact information as above but the Financial Analyst has only the sales price and quantity sold for numerical metrics but does not have the sales revenue per order.
How does the Financial Analyst calculate the sales by branch and product description using SUMPRODUCT?
THE SUMPRODUCT formula would actually be very similar to the way in which we calculated previously. The ONLY difference will be that we need to now multiply two columns against each other instead of just taking the SUMPRODUCT of the sales revenue column. So in this case we have to multiply the sales price and the quantity sold columns against each other.
Example #3 Using the SUMPRODUCT Formula:
The General Manager says job well done. However, he now sends you a file by email which contains product sales over the past 2 years with some sales data and wants you to only give him the breakdown of specific branch location and product sales for 2018. The spreadsheet he sends is very long and below is a sample.
Solution:
We can’t use the SUMIFS formula in this case because we don’t have the sales revenue (unless we manually multiply the sales price and quantity in a separate column). We need to multiple sales price and sales quantity in the formula. Furthermore, the date column is an array and SUMIFS won’t handle if we want to lookup the year 2018 within it.
Therefore, we must use the SUMPRODUCT formula if we want to avoid manual work. Below is a summary of the result of applying the SUMPRODUCT formula.
=SUMPRODUCT((YEAR($C$76:$C$85)=2018)*(($F$76:$F$85)*($G$76:$G$85))*($D$76:$D$85=D95)*($E$76:$E$85=C95))
In this formula, column C is the date column, column F and G are sales price and quantity, column D is product description and column E is care dealer branch.
A summary of the results of the SUMPRODUCT formula being applied is below:
*Note: We used the lookup in the array for year in this example. Had the General Manager wanted us to analyze January, we could have used that instead of 2019 by inserting a month formula = 1 instead of year = 2018.
This is an important step and we are essentially nesting the ‘Year’ formula within the SUMPRODUCT formula to look up the year 2018 and achieve our result. The reason why we did this is because we needed to exclude any other year as the general manager only wanted us to find the year 2018 in particular.
If you wanted to use month = January and year = 2018, this could be achieved with the following formula where we use both the MONTH and YEAR formula nested within the SUMPRODUCT formula:
=SUMPRODUCT((MONTH($C$76:$C$85)=1)*(YEAR($C$76:$C$85)=2018)*(($F$76:$F$85)*($G$76:$G$85))*($D$76:$D$85=D102)*($E$76:$E$85=C102))
In this formula, column C is the date column, column F and G are sales price and quantity, column D is product description and column E is care dealer branch.
The SUMPRODUCT formula is flexible to allow for multiple conditions to be applied when calculating the sum of a range and also calculating the SUMPRODUCT of a range. But what if one of the conditions is to match a condition that exists in the table header? E.g. month Date?
Let’s assume that we are provided with the following table of product sales which now contains data for January and February 2019. How can we summarize the product sales for Washington speedometers for January separately from February in two columns?
To summarize the sales quantity data with multiple conditions we would use the SUMIFS. However, if we used the SUMIFS formula, it would require us to pick a specific column to sum e.g. January or February and this does not provide flexibility to calculate extended sales (price x quantity).
With SUMPRODUCT, we can apply all of the above 3 conditions for date filter, product filter, and speedometer and multiply the sales price by the unit sales for the month we’d like to perform a lookup to.
This is achievable by embedding the INDEX and MATCH functions within SUMPRODUCT. To illustrate this, we can see the formula below and we have switched the placement of the monthly results we would like to return.
=SUMPRODUCT((YEAR($C$153:$C$162)=$C$177)*(($F$153:$F$162)*(INDEX($G$153:$H$162,,MATCH($F$176,$G$152:$H$152,0)))*($D$153:$D$162=$D$177)*($E$153:$E$162=$E$177)))
In this formula, we are inserting an INDEX MATCH array instead of selecting the specific sales column (January or February). This is more flexible because now that we have indexed it, we can decide in another area of the sheet or table which month we’d like to pull. In this formula, column C is the date column, column D is product description and column E is care dealer branch, column F is sales price, columns G and H are unit sales for January and February respectively.
Let’s summarize some of the formula we have learned, rank their flexibility, and when they are best used.
Overall, SUMPRODUCT is an overlooked formula that has a lot of flexibility given that it has the ability to look within arrays and ranges and allows you to nest other formulas within it.
It is a highly recommended formula to use and embed into your analysis!
I hope that helps. Please leave a comment below with any questions or suggestions. For more in-depth Excel training, checkout our Ultimate Excel Training Course here. Thank you!
0 Comments