How to Multiply Two Columns and Then Sum in Excel (3 Examples)

Example 1 – Multiply Two Columns and Sum Their Products Using SUMPRODUCT Function in Excel

Multiply column D and column E. The cell range for quantity is D5:D10 and E5:E10 for unit price.

Enter the following formula in a blank cell.

=SUMPRODUCT(D5:D10,E5:E10)

Example 2 – Multiply Two Columns with One Criterion and Then Sum Them

If you want to get the price of monitors over all regions (one criterion) in the following figure, you can use the SUMPRODUCT function by expanding like the following:

=SUMPRODUCT(D5:D10,E5:E10*(B5:B10="Monitor"))

Formula Breakdown:

D5:D10, E5:E10, and B5:B10 are cell ranges of quantity, unit price, and product name respectively.

Array1 = D5:D10 : This is the 1st array of the multiplication.
Array2 = E5:E10*(B5:B10=”Monitor”) : This means to include only those cells of range E5:E10 for which B5:B10 is equal to cell value “Monitor”.

Example 3 – Multiplication and Summing of Two Columns with Multiple Criteria

3.1 In Multiple Columns

If you want to determine the price of monitors in the middle east region(two criteria), you can find the value using the SUMPRODUCT function.

Enter the following formula into cell D12 to calculate the total amount of sales of monitors in the Middle East region.

=SUMPRODUCT(D5:D10*(C5:C10="Middle East"),E5:E10*(B5:B10="Monitor"))

Formula Breakdown:

D5:D10, C5:C10, E5:E10, and B5:B10 are cell ranges of quantity, region, unit price, and product name respectively.

Array1 = D5:D10*(C5:C10=”Middle East” : This is the 1st array of the multiplication and it includes the only cells of range D5:D10 which matches with value “Middle East”.
Array2 = E5:E10*(B5:B10=”Monitor”) : This means to include only those cells of range E5:E10 for which B5:B10 is equal to cell value “Monitor”.

You have the sum product under multiple criteria in multiple columns.
For iterative search, you can create cell references for the criteria to calculate the sum product.
I have inserted two criteria in cells B14 and C14: one is the region name and the other is the product.
Enter the following formula into cell D13 to calculate the total sales of the specified product at the specified region.

=SUMPRODUCT(((B5:B10=B14)+(C5:C10=C14)),D5:D10,E5:E10)

3.2 In the Same Column

If you want to get the total sales of two or more products of any region, you have to make multiple criteria in the same column. Here, I have calculated the total sales of Monitor and Keyboard in all regions.

Enter the product names for which you want to calculate total sales in cells C14 and C15.
Enter the following formula to calculate the total sales of the selected products in all regions:

=SUMPRODUCT(((B5:B10=C14)+(B5:B10=C15)),D5:D10,E5:E10)