Excel SUMPRODUCT for Two Columns: Formula and Examples

Formulas & functions · Updated November 18, 2026

To multiply corresponding values in two Excel columns and add the products, use =SUMPRODUCT(A2:A5,B2:B5). If A2:A5 contains 2, 3, 4, 5 and B2:B5 contains 10, 12, 8, 15, the result is 163: Excel calculates 2×10 + 3×12 + 4×8 + 5×15.

What SUMPRODUCT does

SUMPRODUCT is an Excel function that multiplies values in matching positions across one or more arrays, then sums the resulting products. With two columns, it pairs the first value in one range with the first in the other, the second with the second, and so on. This makes it useful for totals where each row has a quantity and a per-unit amount, hours and rates, or units sold and unit prices.

The name can be read literally: the function returns the sum of products. It does not simply add the two columns, and it does not multiply the grand totals of the columns. For example, multiplying the sum of quantities by the sum of prices would combine unrelated row pairings and usually produce a different answer.

The basic two-column formula

Assume the first column has quantities in A2:A5 and the second has prices in B2:B5, with a header in row 1. Enter this formula in a result cell:

=SUMPRODUCT(A2:A5,B2:B5)

Excel pairs the values by row, multiplies each pair, and adds those results. In the example above, the calculation is 20 + 36 + 32 + 75, or 163. The comma separates the two array arguments in English-language Excel; in some regional settings, Excel uses a different list separator, such as a semicolon.

You can also express the operation with an explicit multiplication operator. With the same layout—quantities in A2:A5 and prices in B2:B5—the formula is:

=SUMPRODUCT(A2:A5*B2:B5)

Both forms produce the same result for ordinary numeric ranges. The version with separate arguments is a clear starting point, particularly when you later add conditions.

Set up the columns before calculating

For a reliable result, make sure each row represents one matching record. A quantity in row 2 must be paired with its own price in row 2, not with a price from another item. Keep headers above the data rather than including them in the calculation, and use ranges that begin and end on the same rows.

A practical setup might look like this:

  • Column A: item quantity
  • Column B: price per item
  • Column C: optional description or product name
  • Result cell: total value calculated from columns A and B

If rows are added or removed often, consider converting the range into an Excel Table. Select a cell in the data and choose Insert > Table, then confirm whether the table has headers. Tables expand as records are added and make formulas easier to read.

Examples for totals and weighted averages

A common use is a sales total: multiply each line’s units by its unit price, then add the line totals. With units in A2:A4 (2, 3, and 4) and prices in B2:B4 (10, 12, and 8), the formula is:

=SUMPRODUCT(A2:A4,B2:B4)

The result is 2×10 + 3×12 + 4×8 = 88. This calculation is often more convenient than creating a separate extended-price column and summing it, although a separate column can make each line’s calculation visible for review.

SUMPRODUCT can also help calculate a weighted average, where some values count more because they have greater quantities. Assume quantities are in A2:A5 and corresponding prices are in B2:B5. The weighted average price is:

=SUMPRODUCT(A2:A5,B2:B5)/SUM(A2:A5)

For the earlier example, the total weighted value is 163 and total quantity is 14, so the average is about 11.64. This is different from an ordinary average of the four prices because the quantities determine how much weight each price receives.

Multiply and sum only rows that meet a condition

You can use SUMPRODUCT to include only selected rows. For example, suppose column A contains region names, column B contains units, and column C contains unit prices, all from row 2 through row 6. To total the value for rows labeled North, use:

=SUMPRODUCT((A2:A6="North")*B2:B6*C2:C6)

The comparison creates a TRUE or FALSE result for each region. In this calculation, matching rows contribute their units multiplied by their prices; nonmatching rows contribute zero. The text match is not case-sensitive, so North and NORTH are treated as the same text for this purpose.

You can add another condition. With the same layout, this version includes only North rows where units in column B are greater than zero:

=SUMPRODUCT((A2:A6="North")*(B2:B6>0)*B2:B6*C2:C6)

Each condition must evaluate across the same set of rows as the numeric ranges. For many straightforward conditional totals, SUMIFS may be easier to read; SUMPRODUCT is useful when the calculation involves multiplying row-by-row values as well as applying conditions.

Using an Excel Table instead of fixed ranges

Fixed references such as A2:A100 are simple, but they may not include new data entered below row 100. A Table can make a formula adjust automatically as its records grow. Suppose the table is named Sales and has columns named Qty and UnitPrice. The formula for the table’s total is:

=SUMPRODUCT(Sales[Qty],Sales[UnitPrice])

To check a table’s name, click inside it and look for the Table Design tab. The Table Name box on that tab shows the current name; you can change it there. Structured references use the actual table and column names, so spelling and spaces must match. Excel may insert the structured references automatically when you select table columns while entering a formula.

Common problems and how to fix them

  • Ranges have different sizes. A2:A10 and B2:B9 do not contain the same number of positions. Make both ranges cover the same rows, such as A2:A10 and B2:B10.
  • The result is unexpectedly large or small. Check that each row is paired correctly and that the ranges do not include extra data. Confirm that one column contains the intended quantity or weight and the other contains the matching value.
  • Numbers are stored as text. A value that looks like a number may be text after an import or copy-and-paste. Check the cell alignment and warning indicators, then convert the affected entries to numbers before relying on the total.
  • Headers are included. Start the ranges below the header row. This also keeps the formula focused on the records being calculated.
  • The workbook feels slow. Avoid using entire-column references for a large SUMPRODUCT calculation when a bounded range or Table reference will do. SUMPRODUCT evaluates its arrays, so restricting them to the used data can reduce unnecessary work.
  • There is an error in the result. Check for mismatched range sizes, existing errors in source cells, and unexpected text or blanks in the data. Fix the source data where possible rather than hiding an error that might affect the total.

SUMPRODUCT compared with alternatives

For two columns of numbers, SUMPRODUCT is concise and does not require a helper column. A helper column may still be preferable when users need to inspect each row’s product, when the workbook needs a clear audit trail, or when the same line calculations are reused elsewhere. In that setup, calculate each row’s product separately and then sum the product column.

SUM is designed to add values, not to multiply matching rows before adding them. You could combine multiplication and addition with an array formula, but SUMPRODUCT states the intended operation directly and works as a normal formula in current Excel for Microsoft 365. For conditional totals without row-by-row multiplication, SUMIFS is often a more direct choice. The best option depends on whether visibility, simplicity, or a compact calculation matters most.

Frequently Asked Questions

Can SUMPRODUCT multiply two columns of different lengths?
No. The arrays should have matching dimensions so each value has a corresponding value in the other range. Adjust the references so both begin and end on the same rows.

Do I need to press Ctrl+Shift+Enter?
No. In current Excel for Microsoft 365 on Windows, enter a SUMPRODUCT formula normally with Enter. It does not require the older Ctrl+Shift+Enter array-formula entry method.

Can I use SUMPRODUCT for a whole column?
It is possible to reference whole columns, but that can make a calculation do more work than necessary. Prefer a range covering the actual data or a Table reference that grows with the records.

Does SUMPRODUCT ignore blank cells?
Blank positions generally contribute zero to a numeric product, but check your data for cells that contain text, errors, or formulas returning unusual values. Clean, consistently numeric source columns make the result easier to trust.

What is the difference between SUMPRODUCT and multiplying column totals?
SUMPRODUCT multiplies matching values row by row and then adds those products. Multiplying the two column totals combines every value in one column mathematically with every value in the other, rather than preserving the row-by-row pairings.

Does the formula work in Excel for Mac?
SUMPRODUCT is available in Excel for Mac as well as Microsoft 365 for Windows. The formula syntax is the same, though regional list-separator settings can affect whether your installation expects commas or semicolons.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.