How to Multiply Two Columns and Then Sum in Excel

From doing basic Excel calculation to complex ones, we often need to multiply two columns and find their sum in Excel. Apart from generally writing simple multiplication formulas for two columns and dragging them down across columns, we have a strong Function in Excel called the SUMPRODUCT function. As a matter of fact, in this article, we will be learning different ways to multiply two columns and sum them in excel by adding criteria as well for a large volume of data.

📁 Download Excel File

Download the Excel file below.

What Is the SUMPRODUCT Function in Excel?

The SUMPRODUCT function gives the sum of the products of the corresponding range of arrays that are mentioned in the formula. The syntax of the function is as follows:

Where,

array1 = The first array arguments whose components we want to multiply and add. This array argument is compulsory.

[array 2], [array3] = These are array arguments that can range from 2 to 255 in number. These are optional.

An important note about array arguments is that they have to be of the same dimension.

Learn to Multiply Two Columns and Then Sum in Excel with These 3 Approaches

From the following dataset, we will learn to multiply two columns and find their sum in excel with 3 separate examples. Let us suppose that a shop has different goods that we need in our day-to-day life and thereby we are given their unit price along with the quantity of stock. Using this dataset, we will first find the net worth of all the commodities that are present in that shop. We can find the net worth of all the commodities by using the SUMPRODUCT function across the range of cells that have their quantity and their unit prices. Here is the dataset for illustrating things clearly.

We will use the SUMPRODUCT function and the range of cells to find out the price of all commodities. The steps for these are as follows:

⬇️⬇️ STEPS ⬇️⬇️

• We go to the required cell where we want to show the output and select the cell.
• After selecting the cell, we move to the formula bar and write the following formula down.

• Here D5:D11 indicates the range of cells for the quantity of different commodities and E5:E11 is the range of cells for unit price. After writing the formula, we press Enter.
• The function will show the product of all commodities as follows:

📕 Read More: 5 Easy Ways to Fix When SUM Time Is Not Working in Excel

If we are to multiply two columns with one criterion, then we have to modify the array argument inside the SUMPRODUCT function a little. Let us specify a criterion in the sample datasheet. Suppose we want to find out the vegetable category from all the commodities present and find their total worth value by multiplying the quantities with unit prices and then adding them. We can specify the criterion by the following steps:

   ⬇️⬇️ STEPS ⬇️⬇️

• To filter Vegetable category from all the categories, we specify the argument (C5:C11=”Vegetables”) inside the SUMPRODUCT function.
• We select the cell where we want to find the sum of the product. Then we write the following formula down

• After writing the function, we press Enter.
• The following table will give a picture of the output of the Price of all vegetables.

Now we will multiply two columns with multiple criteria (in different columns) and thereby find their sum. Let us say that we will be filtering the criteria “Tuna” from the type of commodities column and “Fish” from the Category column. To find this product sum, first, we need to specify the argument inside that we need to filter out. We can do this by the following steps:

⬇️⬇️ STEPS ⬇️⬇️

• Let us filter Tuna from the first column. We can do it by writing the argument (B5:B11=”Tuna”)
• Similarly, we can specify Fish from the second column by writing the argument (C5:C11=”Fish”)
• Now we incorporate this overall in the SUMPRODUCT formula and write the formula in the following way:

• The price of all Tuna Fish will be shown in the following table:

How to Multiply Two Columns and Then Sum Based on Multiple Criteria in the Same Column

Now let us say that we need to specify multiple criteria in the same column. For example, in this case, we want to find the total worth of Tuna and Salmon fishes altogether. To do this, we write the two criteria in two cells and correspondingly modify the formula. The steps for this case are as follows:

• Firstly, we list Tuna from the type of commodities column by the argument (B5:B11=B14) where B14 is the cell where the criterion Tuna is written and referenced accordingly.
• Secondly, we list Salmon from the same column by the argument (B5:B11=B15) where B15 is the cell where the criterion Salmon is written and referenced.
• Then above-mentioned two arguments are added with a + sign in the middle to indicate that these two criteria are to be considered together.
• After that we move on to the cell where we want our output and write the modified formula as shown below:

• So, the result will show the price of all Tuna and Salmon fish as the following table:

📕 Read More: 5 Suitable Ways to Multiply a Column in Excel by a Constant

📄 Important Notes

🖊️ We should consider enclosing the array arguments inside the SUMPRODUCT function inside parentheses or brackets when we use arithmetic operators like the + sign

🖊️ Using parentheses or brackets, we should group together the separate array arguments.

🖊️ The array arguments in the SUMPRODUCT function needs to be of the same dimension for the function to work 

📝 Takeaways from This Article

Therefore, you have taken the following summed-up inputs from the article:

📌 SUMPRODUCT function can be used to find the product and sum of large volumes of data and to filter specific entries from a large volume of data to find the sum of the product.

📌 This filtering criteria is applicable both across different rows and across different columns.

📌 The array arguments inside the SUMPRODUCT function can be up to 255 in number and we should group the arguments in brackets. Also, the first argument is essential whereas the others are optional. 

Conclusion 

This article will hopefully help you to find the sum of products of different volumes of data and help you get familiar with the structure and use of a simple but important function called the SUMPRODUCT function. However, if you have any further queries, you can comment on the article or reach out to us. Follow our page ExcelDen to get more insights about different Excel-based information.