When working with inventory, sales data, or complex calculations in Microsoft Excel, you frequently encounter situations where you need to multiply numbers in one column by numbers in another column, and then add all those results together.
Normally, you would create a “helper column” to do the multiplication row-by-row, and then use the SUM function at the bottom. The SUMPRODUCT function eliminates the need for that extra column entirely, performing the multiplication and the addition in one swift, hidden calculation.
Understanding the Basics of SUMPRODUCT
The name of the function explains exactly what it does: it calculates the product (multiplication) of arrays, and then calculates the sum (addition) of those products.
Syntax: =SUMPRODUCT(array1, [array2], [array3], ...)
- array1: The first range of cells you want to multiply.
- array2: The second range of cells you want to multiply against the first.
Note: The ranges you select must be exactly the same size. You cannot multiply a range of 10 cells against a range of 5 cells.
Example: Calculating Total Inventory Value
Imagine you have a small spreadsheet for a hardware store. Column B lists the “Quantity in Stock” and Column C lists the “Price per Item.” You want to find the total financial value of all the inventory in the warehouse.
The Slow Way (Using a Helper Column):
You would go to Column D, write =B2*C2, drag that formula down to row 100, and then write =SUM(D2:D100) at the bottom.
The Fast Way (Using SUMPRODUCT):
- Click on an empty cell where you want the final total value to appear.
- Type the formula:
=SUMPRODUCT(B2:B100, C2:C100) - Press Enter.
Excel will instantly multiply B2 by C2, B3 by C3, B4 by C4 (and so on), and then add all those individual answers together to give you one massive final total. No messy helper columns required!
Using SUMPRODUCT with Three or More Columns
The function is not limited to just two columns. You can multiply three or more columns together before summing them up.
For example, if Column B is “Quantity”, Column C is “Price”, and Column D is “Discount Percentage” (formatted as a decimal), you can calculate the total discounted value of the inventory like this:
=SUMPRODUCT(B2:B100, C2:C100, D2:D100)
Excel will multiply Row 2 (Quantity * Price * Discount), then Row 3, and add everything together perfectly.