If quantities are in B2:B5 and unit prices are in C2:C5, use =SUMPRODUCT(B2:B5,C2:C5) to multiply each quantity by its matching price and add the products. If you mean “add a range, then multiply that total by one factor,” use SUM with multiplication, such as =SUM(B2:B5)*D1.
What “SUM times” can mean in Excel
The phrase “SUM times formula” can describe two different calculations. The first multiplies one total by a factor: for example, sum a set of hours and then apply an hourly rate. The second multiplies corresponding values in two columns, then adds those row-level results: for example, calculate each product’s quantity times its price and find the order total. Choosing the right formula depends on which calculation you intend.
Excel’s SUM function adds numbers; it does not multiply values by itself. The multiplication operator is an asterisk. SUMPRODUCT is designed for the second pattern because it multiplies corresponding items in one or more arrays and sums the results. Keeping those jobs separate makes formulas easier to understand and helps prevent a plausible-looking but incorrect total.
- For one total multiplied by one factor, use
=SUM(range)*factor. - For products calculated row by row and then added, use
=SUMPRODUCT(first_range,second_range).
Use SUMPRODUCT for row-by-row multiplication
SUMPRODUCT pairs the first item in one range with the first item in another, multiplies them, and repeats that operation down the ranges. It then adds all of those products. With quantities in B2:B5 and prices in C2:C5, it calculates B2 times C2, plus B3 times C3, plus B4 times C4, plus B5 times C5.
For that layout, enter this formula in the cell where you want the total:
=SUMPRODUCT(B2:B5,C2:C5)
For example, suppose B2:B5 contains 2, 3, 1, and 4 units, while C2:C5 contains prices of 5, 7, 12, and 3. The calculation is 2×5 + 3×7 + 1×12 + 4×3, which equals 55. The result is 55 in the same units as the multiplied values—for example, dollars if the prices are dollars per item.
This approach is useful for totals that depend on multiple line items, including quantities and unit costs, hours and hourly rates, or units sold and commission per unit. It avoids adding a separate multiplication formula in every row just to calculate a grand total. The source values remain visible, and changing any quantity or rate updates the result.
Multiply a sum by one factor
If you first need the total of a single range and then need to multiply that total by one number, place the SUM function in parentheses conceptually by applying the multiplication to the completed SUM expression. Excel evaluates the SUM function and then multiplies its result by the factor. For example, with hours in B2:B5 and a single rate or multiplier in D1, use:
=SUM(B2:B5)*D1
If B2:B5 contains 12, 18, 25, and 5, the hours add to 60. If D1 contains 1.1, the formula returns 66. That multiplier could represent a 10% increase, because 1.1 is 110% of the original total. If D1 instead contains 20, the formula returns 1,200. Make sure the factor represents the operation you intend; multiplying by 10 adds ten times the total, whereas adding 10% requires a factor of 1.1.
You can also type a fixed factor directly into the formula. For example, if the values to add are in B2:B5 and the intended multiplier is 3, the expression is =SUM(B2:B5)*3. A cell reference is usually more convenient when the factor may change: edit the value in D1 rather than changing the formula itself. If the formula will be copied elsewhere, consider whether that reference should move with the formula or remain fixed.
Build and check a line-item total
To use the row-by-row method in a worksheet, set up one row per item and keep the paired inputs on the same row. A simple layout might put a product name in column A, quantity in B, and unit price in C. Place the total formula below the table or in a clearly labeled summary cell.
- Enter each quantity and its matching unit price on the same row.
- Confirm that the ranges begin and end on the same rows, such as B2:B5 and C2:C5.
- Enter the SUMPRODUCT formula in the total cell.
- Check one or two rows by multiplying their quantity by their price, then compare the hand-calculated total with Excel’s result.
- Format the result as currency or another suitable number format if that makes the value easier to read.
For instance, a worksheet with quantities in B2:B5 and prices in C2:C5 uses =SUMPRODUCT(B2:B5,C2:C5) for the total. If you later add a fifth item, extend both ranges to row 6. A formula that includes the new quantity but not its corresponding price—or vice versa—will not represent the full table accurately. Keeping the two ranges aligned is one of the most important checks for this calculation.
Choose the right formula for your data
Use the calculation’s meaning—not just the shape of the data—to decide. If every value in a range is simply being added and that sum is then adjusted by one common factor, SUM followed by multiplication is direct. If each row has its own paired values that need to be multiplied before the products are added, SUMPRODUCT matches the task.
- Total hours at one rate: add the hours, then multiply by the single hourly rate.
- Several items at different prices: multiply each quantity by its own price, then add those line amounts.
- One subtotal with a percentage increase: multiply the subtotal by 1 plus the percentage, such as 1.1 for a 10% increase.
- Separate row calculations needed for review: calculate each line in a helper column, then sum that column. This makes individual amounts visible but uses more cells.
SUMPRODUCT can also accept more than two arrays, multiplying corresponding entries across the arrays before summing. For example, a calculation involving quantity, unit price, and a per-row adjustment factor can use three aligned ranges. That extra complexity is only helpful when the model calls for it; for a straightforward calculation, two clearly labeled columns and one SUMPRODUCT formula are easier to audit.
Common errors and how to troubleshoot them
A wrong answer often comes from range alignment or data quality rather than from the basic formula. Compare the first and last row numbers in each range. If quantities use B2:B20 but prices use C2:C19, the inputs are not paired consistently. For reliable results, make the ranges the same size and ensure each row represents the same item in every column.
- Unexpectedly low total: check that the ranges include every intended row and that no quantity or price was left outside the selected range.
- Unexpectedly high total: look for duplicated rows, a factor entered in the wrong place, or values already multiplied in a source column.
- Numbers stored as text: entries that look numeric may not behave as expected in calculations. Check how the values were imported or entered, and convert them to numbers where appropriate.
- Blank cells: a blank in one paired range contributes no meaningful product for that row. Decide whether the blank means zero, missing information, or an incomplete record before trusting the total.
- Parentheses and order: for a sum multiplied by a factor, keep the intended operation clear. SUM already groups its range, so
=SUM(B2:B5)*D1adds B2:B5 before multiplying by D1.
Formatting can also create confusion. A cell formatted as currency displays a currency symbol and decimal places, but number formatting does not change the underlying calculation. Conversely, a result displayed with zero decimal places may conceal cents even when the stored result has them. Check the formula and the underlying values before assuming the displayed rounding is the full result.
Use a helper column when you need visible line amounts
SUMPRODUCT is compact, but a helper column may be a better choice when someone needs to inspect each row’s calculation. With quantities in B2:B5 and unit prices in C2:C5, you can calculate each line amount in D2 and fill the formula down through D5. The row formula for D2 is:
=B2*C2
For the row 2 values, this displays that item’s quantity times its price; copying it down adjusts the row references for each subsequent item. Then sum the line amounts in D2:D5. With line amounts in D2:D5, the total formula is:
=SUM(D2:D5)
This layout makes it easy to find which item contributes a particular amount, review exceptions, or add row-specific adjustments. It also creates more formulas and a column that must be maintained as new rows are added. SUMPRODUCT is often tidier for a small, fixed calculation; helper columns can be clearer when the worksheet serves as a detailed report or audit trail.
Frequently Asked Questions
How do I multiply a SUM formula in Excel?
If the values are in B2:B5 and the multiplier is in D1, enter =SUM(B2:B5)*D1. Excel adds B2:B5, then multiplies that total by D1.
How do I multiply two columns and add the results?
If the first set of values is in B2:B5 and the matching second set is in C2:C5, enter =SUMPRODUCT(B2:B5,C2:C5). It multiplies values on matching rows and adds those products.
Can I use SUMPRODUCT for quantity times price?
Yes. Put each item’s quantity and unit price on the same row, then use SUMPRODUCT with the two equal-sized ranges. It returns the sum of all quantity-times-price line amounts.
Why is my SUMPRODUCT result wrong?
Check that the ranges cover the same rows, that each pair belongs together, and that the source values are numeric. Also look for missing or duplicate rows and make sure you have not included an already-calculated line total as an input.
Should I use SUMPRODUCT or a helper column?
Use SUMPRODUCT for a concise total when you do not need to display each product. Use a helper column when row-level amounts should remain visible for checking, reporting, or further calculations.
Related
- Excel NOW as Text: Show the Current Date and Time
- Excel Current Year: How to Get It with NOW and TODAY
- Excel NOW Date: How the Current Date and Time Work
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel IF OR Formula: Combine Multiple Conditions EasilyTo use Excel’s IF with OR, put OR in IF’s logical-test position: Excel checks whether at least one…
- VLOOKUP Formula to Compare Two Sheets in Excel EasilyTo compare values on two Excel sheets by a shared ID, enter this formula on the first sheet,…
- COUNTIF Excel Blank? Count Empty Cells CorrectlyTo count blank cells in Excel, use =COUNTIF(A2:A20,""), replacing A2:A20 with your range. COUNTIF can also count cells…
- Excel Formula Between Two Dates: Count, Sum, or CheckIf dates are in A2:A100, the start date is in D1, and the end date is in E1,…
- TEXTJOIN Excel Unique Values: Formula, Sorting, and TipsYes. In Microsoft Excel for Microsoft 365, combine TEXTJOIN with UNIQUE to put distinct values from a range…
- Excel Set Print Area: How to Print the Right CellsTo set a print area in Microsoft Excel for Microsoft 365 on Windows, select the cells you want…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.