Function Problems

Multiply Quantities by Prices and Sum the Total in Excel

John WilsonJohn Wilson Nov 18, 2026 869 views

Question details

Calculate a total by multiplying each product quantity by its corresponding price and adding the results.

Multiply Quantities by Prices and Sum the Total in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet lists quantities and matching ingredient prices in corresponding cells.
Observed behavior
The desired result is a single total, such as 1 × 1 + 2 × 5 = 11.
Before you start

Identify the quantity and price ranges, and make sure they have the same dimensions and matching positions. Check that the quantities and prices are entered as numbers.

Solution 1Recommended

Use SUMPRODUCT to calculate the total

Use one formula to multiply corresponding quantities and prices and add the products.

1
Arrange the values

Enter quantities in B2:D2 and their corresponding prices in B3:D3, keeping each quantity aligned with its price.

2
Enter the formula

Select an empty cell and type =SUMPRODUCT(B2:D2,B3:D3). Replace the example ranges with your actual quantity and price ranges if needed.

3
Calculate the total

Press Enter. Excel multiplies each quantity by its matching price and displays the sum of the products.

Use SUMPRODUCT to calculate the total
Keep the ranges the same size: Both ranges must have matching dimensions. For example, B2:D2 and B3:D3 each contain three cells.
Calculate totals in WPS Spreadsheets

Calculate quantity and price totals in WPS Spreadsheets

Use SUMPRODUCT in WPS Spreadsheets to multiply matching quantities and prices and add their results in a single cell.

  1. 1. Open the worksheet: Open WPS Spreadsheets and select Open to load your spreadsheet.
  2. 2. Check the ranges: Locate the quantity and price cells and ensure each quantity aligns with its corresponding price.
  3. 3. Enter SUMPRODUCT: Select an empty cell and enter =SUMPRODUCT(B2:D2,B3:D3), replacing the ranges with your own.
  4. 4. View the result: Press Enter to display the total.
Calculate a total from matching quantities and prices with one formula.Open and save Microsoft Excel XLSX files.Edit spreadsheets in a familiar, easy-to-use interface.
microsoft office alternative - wps office

Frequently Asked Questions

What formula multiplies quantities by prices and adds the results?

Use =SUMPRODUCT(quantity_range,price_range). For quantities in B2:D2 and prices in B3:D3, enter =SUMPRODUCT(B2:D2,B3:D3) in an empty cell.

Can I use SUMPRODUCT when quantities and prices are in columns?

Yes. If quantities are in B2:B5 and prices are in C2:C5, enter =SUMPRODUCT(B2:B5,C2:C5). Each quantity must be on the same row as its price.

Why does my SUMPRODUCT formula return an error?

Check the ranges in the formula: they must have the same dimensions. For example, B2:B5 and C2:C4 have different numbers of cells and need to be corrected.

How do I calculate the same total in WPS Spreadsheets?

Open the worksheet in WPS Spreadsheets, select an empty cell, and enter the same SUMPRODUCT formula using your quantity and price ranges. Press Enter to calculate the total.