Excel SUM and Multiply: How to Combine Them

How-to basics · Updated November 18, 2026

Excel’s SUM function adds numbers; it does not multiply them. To multiply values in matching rows and add the products, use SUMPRODUCT; to multiply a total by a factor, use SUM with the multiplication operator, as in =SUM(A2:A5)*B1.

What “SUM multiply” can mean

People asking how to “sum multiply” in Excel may mean one of two different calculations. The first is to multiply each pair of values in corresponding rows, then add those row-by-row results. For example, a sales total might require multiplying each item’s quantity by its unit price and then adding all the line totals. The second is to add a range and then multiply that sum by one number, such as a tax rate or adjustment factor.

These calculations use different formula patterns. Identifying which one you need is important: multiplying the sum of two columns is not generally the same as summing the products of matching rows. Excel’s multiplication operator is an asterisk (*), while SUM and SUMPRODUCT are functions with distinct jobs.

  • Multiply matching pairs, then add: use SUMPRODUCT.
  • Add a range, then multiply its total: use SUM(range)*factor.
  • Multiply values and keep each result: multiply cells in a separate column, then sum that column if needed.

Multiply matching rows and add the products

SUMPRODUCT multiplies corresponding entries in one or more arrays and adds the resulting products. It is a natural choice for totals based on quantities and prices, hours and hourly rates, or units and per-unit costs. The ranges should cover the same number of rows and columns so that Excel can match each value with its intended partner.

Suppose A2:A5 contains quantities 3, 2, 5, and 1, while B2:B5 contains unit prices $4.50, $7.00, $1.20, and $12.00. With quantities in A2:A5 and prices in B2:B5, enter =SUMPRODUCT(A2:A5,B2:B5). The row products are $13.50, $14.00, $6.00, and $12.00, so the formula returns $45.50.

The comma between the ranges tells Excel to pair their entries: A2 with B2, A3 with B3, and so forth. The formula returns one total, rather than displaying each intermediate product in the worksheet. You can format the result cell as Currency using Home > Number controls; formatting changes how a result is displayed, not the arithmetic performed.

Add first, then multiply the total

If your intent is to total a list and then apply one multiplier to that total, place the multiplication outside SUM. For instance, suppose A2:A5 contains costs of 12, 18, 25, and 5, and B1 contains an adjustment factor of 1.08. With those values in the stated cells, enter =SUM(A2:A5)*B1. The values add to 60, and 60 multiplied by 1.08 is 64.8.

This structure is useful when one rate or factor applies to the entire total. If B1 instead contains 8% as a percentage, the formula multiplies the sum by 0.08 and returns 4.8—the amount of the increase, not the original total plus the increase. To calculate a total with an 8% increase included, use a multiplier of 1.08, or add the increase to the original sum. For example, with the same range and B1 containing 8%, enter =SUM(A2:A5)*(1+B1); the result is 64.8.

Be clear about whether a factor represents the full multiplier or just the added percentage. A factor of 1.08 means 108% of the starting amount; a rate of 8% means only the additional 8%. This distinction prevents a frequent spreadsheet mistake where a formula calculates the increase amount but is mistaken for the adjusted total.

Build a per-row calculation

Sometimes it is more useful to see every multiplication result than to calculate only a grand total. For a simple table, put the row calculation in a new column and copy it down. If A2:A5 contains quantities and B2:B5 contains prices, enter the following in C2, where the first row’s quantity is in A2 and its price is in B2: =A2*B2. For example, if A2 is 3 and B2 is 4.50, C2 returns 13.50.

To repeat the calculation for the other rows, select C2 and drag the fill handle (the small square at the lower-right corner of the selection) down through C5. Excel adjusts the relative references for each row, producing formulas equivalent to A3 times B3, A4 times B4, and A5 times B5. To add the line totals, with the calculated values in C2:C5, enter =SUM(C2:C5); for the example above, it returns 45.50.

This two-step layout is often easier to audit than one total formula because the worksheet shows which row contributes each amount. It also makes it easier to spot a wrong price or quantity. If you only need the final total and do not need to inspect line results, SUMPRODUCT can do the same pairwise multiplication and addition in one formula.

Multiply and add only rows that meet a condition

SUMPRODUCT can also total products for rows that match a criterion. Imagine A2:A5 contains region names, B2:B5 contains units, and C2:C5 contains prices. Let the rows be North, South, North, and West, with units 3, 4, 2, and 5 and prices 4, 6, 10, and 3. To total only the North rows, with the region labels in A2:A5, units in B2:B5, and prices in C2:C5, use =SUMPRODUCT((A2:A5="North")*B2:B5*C2:C5). It returns 32: 3 × 4 plus 2 × 10.

The comparison produces a true-or-false result for each region; in this multiplication, Excel treats the matching results as 1 and nonmatching results as 0. Products from nonmatching rows therefore contribute zero to the total. The criterion text must match the values in the region column, including any meaningful spaces. For a changing criterion, put the region name in a cell instead of typing it into the formula. With the region to find in E1 and the same data ranges, use =SUMPRODUCT((A2:A5=E1)*B2:B5*C2:C5). If E1 contains North, the result is still 32.

For straightforward conditional addition, Excel also provides functions such as SUMIF and SUMIFS. Those functions are designed to add values that meet criteria; when the amount to add is itself a product of columns, SUMPRODUCT offers a direct way to combine the condition and multiplication.

Choose the right formula

Use the simplest formula that matches the calculation and makes the worksheet easy to understand. A single multiplication is clearest with *. A total multiplied by one factor is clearest with SUM followed by *. A total of row-by-row products is usually clearest with SUMPRODUCT. A visible helper column can be preferable when people need to review or edit individual line calculations.

Excel also has a PRODUCT function, but it does something different: it multiplies all the numbers supplied to it. With 2, 3, and 4 in A2:A4, enter =PRODUCT(A2:A4) to get 24. That is not a substitute for adding the products of two matching ranges. For a quantity-and-price total, use SUMPRODUCT rather than multiplying all quantities together and all prices together.

For a compact comparison, consider what the expression should return:

  • One row’s extended price: multiply that row’s quantity by its price.
  • All extended prices added together: use SUMPRODUCT or a helper column with SUM.
  • A list total scaled by one number: use SUM(range)*factor.
  • The product of every number in one range: use PRODUCT.

Common errors and how to fix them

If SUMPRODUCT returns a surprising number, first check that both ranges start and end on the same rows. A quantity range of A2:A10 paired with a price range of B2:B9 does not describe matching sets of records and can cause an error. Also check that the formula refers to the intended columns and that the source values are numbers rather than text that only looks numeric.

Watch for extra spaces or inconsistent labels in a condition. “North” and “North ” may look nearly identical in cells but are not the same text. If a value that should be included is missing, inspect the source cell and the criterion. Also verify that a factor is entered in the form you intend: 8% is stored as 0.08, whereas 1.08 represents 108%.

Another common issue is confusing the order of operations. Excel evaluates multiplication before addition, but parentheses make the intended grouping explicit. For a total multiplied by a factor, =SUM(A2:A5)*B1 is clear. If you instead multiply individual entries before adding them, use SUMPRODUCT or a helper column; do not assume that a formula summing one range and multiplying by another range will pair the entries as intended.

Finally, check the displayed number format. A result of 45.5 may appear as 45.50 in Currency format or 46 if the cell is set to show no decimal places. The underlying value can still be 45.5. Increase the displayed decimal places or inspect the formula bar if a rounded display makes it difficult to verify a calculation.

Frequently Asked Questions

Can SUM multiply numbers in Excel?
SUM adds values; it is not a multiplication function. Combine it with the * operator to multiply a sum by a factor, or use SUMPRODUCT to multiply corresponding entries and add the products.

How do I multiply two columns and sum the results?
If the matching values are in A2:A10 and B2:B10, enter =SUMPRODUCT(A2:A10,B2:B10). Excel multiplies each pair of values in the same row and adds those products into one result.

How do I multiply a sum by a percentage?
For values in A2:A10 and a percentage in B1, =SUM(A2:A10)*B1 calculates the percentage amount when B1 contains a rate such as 8%. To return the original total plus that increase, use =SUM(A2:A10)*(1+B1).

Why is my SUMPRODUCT formula returning zero?
Check whether the paired ranges contain numeric values and whether they cover the same rows. If the formula has a condition, confirm that its text matches the cells exactly and that the criterion points to the intended value.

Can I see each multiplication before adding the total?
Yes. Multiply the first row’s two cells in a helper column, fill the formula down, and use SUM on that column. This makes each line result visible for review.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.