

The SUMPRODUCT formula in Excel is quite powerful. The disadvantage: SUMPRODUCT is often not self-explanatory. Before Excel version 2007 it was used as the SUMIFS formula. Fortunately, with Excel 2007 the SUMIFS formula replaced SUMPRODUCT in many cases. But there are still some cases, in which you have to use SUMPRODUCT. Here is everything you should know about the formula in Excel.
Contents
The SUMPRODUCT formula sums up all values after multiplying cell ranges with each other.
You might wonder, why this is important in terms of lookups? There are two reasons:
SUMPRODUCT has one special characteristic. It is not an array formula by definition because it doesn’t require you to press Ctrl + Shift + Enter after typing. However, it deals with arrays so that the usage is quite similar to array formulas.
Structure of the SUMPRODUCT formula
The structure of the SUMPRODUCT formula is quite simple. You just provide at least one and at most 255 ranges of cells as shown in the image on the right-hand side.
Calculation steps of the SUMPRODUCT formula
The image on the right-hand side illustrates how the formula works. Say, you have the two ranges A1 to A6 and B1 to B6. SUMPRODUCT multiplies cell A1 with B1, A2 with B2 and so on. Afterwards, it sums up all single result.
Example 1 for the SUMPRODUCT formula.
As a first example, you explore the basic usage of the SUMPRODUCT formula: Multiplying to cell ranges and summing up the result.
Say, you have sales data as shown in the screenshot on the right-hand side. Besides the product and region, you also have the amount per product and region as well as the price for each combination of product and region. You want to calculate the total revenue.
That means, you have to multiply the amount with the price for each combination of product and region, in this case for each row. You could achieve this with an additional helper column. Alternatively, you could just use the SUMPRODUCT formula.
The SUMPRODUCT formula for this example is shown in cell C11 of the screenshot on the right-hand side. You only have to put both cell ranges (amount and price) into the SUMPRODUCT formula. The resulting formula is
=SUMPRODUCT(D4:D9,E4:E9) .
Hold on a second. Was this information helpful so far?
...or on other networks!
Twitter: Follow @professorexcel
Facebook:
Example 2 for the SUMPRODUCT formula.
Besides the basic usage as shown in the previous example, you can use the SUMPRODUCT formula for lookups. The basic idea is that you multiply ranges by 1 if a condition is met and by 0 if not.
Please take a look at the image on the right-hand side. You have a simple table listing products and regions in columns B and C as well as the price listed in column E. You want to return the price from column E by selecting a product and region.
The approach is quite simple.
Applying this on the example above leads to the following formula:
=SUMPRODUCT((B4:B9=C15)*(C4:C9=C16)*(E4:E9))
Calculation steps for the SUMPRODUCT formula.
Here is what happens in the background: Through the multiplication of each argument, TRUE arguments are converted to the number 1 and FALSE arguments to 0. Only if all arguments return TRUE (which means 1), the product is not 0. In the example above, that’s only the case for the third row so that the result is 22,000.
Please note: The arguments of the SUMPRODUCT formula can also be entered slightly different. Instead of using the *-sing, you can separate the arguments with a comma. In this case you have to make sure that the resulting TRUE and FALSE arguments are converted to number by either multiplying them by 1 or using the double minus sign (“–“). The formula could look like this:
=SUMPRODUCT(–(B4:B9=D15),–(C4:C9=D16),E4:E9)
Example 3 for the SUMPRODUCT formula.
In a third example for the SUMPRODUCT formula you want to combine the previous two examples. The goal is to return the sum of products if two criteria are fulfilled like shown on the image on the right-hand side.
You want to multiply the amount of each product and region by the price. The result is the revenue for each product and region. Please follow the same rules as before:
Applying these rules on the example above results in the following formula (cell C22):
=SUMPRODUCT(($B$4:$B$9=C$21)*($C$4:$C$9=$B22)*($D$4:$D$9)*($E$4:$E$9))
The calculation steps for the fomula in cell C22 are shown in the picture on the right-hand side.
Calculation steps for the SUMPRODUCT formula.
Also in this case, you can slightly transform the formula and divide each argument by comma.
=SUMPRODUCT($D$4:$D$9,$E$4:$E$9,–($C$4:$C$9=$B28),–($B$4:$B$9=C$27))
Was the information helpful in this article?
'Professor Excel Tools': Add more than 60 amazing features to Excel!
Professor's Error Helper for Excel: Solve any error in Excel.
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
Subscribe to our popular Excel newsletter! You get all this:
Your welcome gift: Our big 45 pages keyboard shortcuts package.
Subscribe now! In case the sign-up form above doesn't work, please use this page. Sorry for the inconvenience.
Please use this download link to download the Currency Converter Excel add-in!
Please use this download link to download the comment extraction example: http://bit.ly/Extract_Comment_Example
Comments 1
RAMIT
2nd one did not work its showing value