SUMPRODUCT Function with Multiple Columns in Excel (4 Simple Ways)

SUMPRODUCT function is one of the most powerful array functions in Microsoft Excel that can pull out data from an array based on the criteria along multiple columns & rows. In this article, you’ll learn how to use this SUMPRODUCT function efficiently with multiple columns under a number of criteria.

Download Practice Workbook

You can download our Excel workbook that we’ve used to prepare this article.


Introduction to SUMPRODUCT Function

Before getting down to the uses of the SUMPRODUCT function, let’s have a look at first how this function works.

  • Syntax:

=SUMPRODUCT(array1,[array2],[array3],…)

  • Function:

Returns the sum of the products of corresponding ranges or arrays.

  • Example:

In our example below, there are two columns comprising of unit & cost per unit of some materials. With SUMPRODUCT function, we can easily find out the total cost for all units. To do this, we have to type in Cell C7:

=SUMPRODUCT(B3:B6*C3:C6)

After pressing Enter, you’ll get the total cost at once for all products.

sumproduct introduction

Inside the SUMPRODUCT function, we’re technically multiplying all units from Column B with the related costs per unit from Column C. SUMPROUCT function will then return by summing up all the products.


4 Suitable Approaches to Use SUMPRODUCT with Multiple Columns

1. Using SUMPRODUCT with Multiple Columns under AND Logic Criteria

Now we’ll deal with our actual dataset where 3 columns are present with computer brands, device categories & sales price for each. In our first application with SUMPRODUCT function, we’ll find out the total sales price of Lenovo notebook from the table data.

📌 Steps:

➤ Select Cell G15 & type:

=SUMPRODUCT((B5:B27=G12)*(C5:C27=G13)*(D5:D27))

➤ Press Enter & you’ll get the result at once.

sumproduct with multiple columns and logic criteria

So how this function works, right? With B5:B27=G12 & C5:C27=G13 inside the function, we’re defining the brand & device names & D5:D27 is the output range that will return based on the brand & device criteria.

With SUMPRODUCT function, we can also extract the total counts of Lenovo notebook or any other category from the table.

📌 Steps:

➤ Select Cell G18 & type:

=SUMPRODUCT(--(B5:B27=G12),--(C5:C27=G13))

➤ Press Enter & you’ll see the result right away.

sumproduct with multiple columns and logic criteria

In this case, we’re using logic arguments inside the SUMPRODUCT function. We’re asking the function to determine if the selected criteria match with the cell values in Column B & C. Uses of Double-Unary(–) before the arguments will convert the logical text values (TRUE or FALSE) into 1 or 0. And then SUMPRODUCT will sum up those number values to display the final result.


2. Using SUMPRODUCT with Multiple Columns under OR Logic Criteria

Now we’ll assign OR logic in our criteria. We’ll determine the total sales price of Lenovo notebook & desktop.

📌 Steps:

➤ In Cell G15, our formula based on the criteria will be:

=SUMPRODUCT((B5:B27=G11)*((C5:C27=G12)+(C5:C27=G13))*(D5:D27))

➤ Press Enter & the total sales price will be displayed as well.

sumproduct with multiple columns or logic criteria

If we want to count the presence of Lenovo devices in the table, then the device criteria will have to be added by inserting a Plus(+) between those two criteria.

📌 Steps:

➤ In Cell G18, the formula will be:

=SUMPRODUCT(--(B5:B27=G11),--((C5:C27=G12)+(C5:C27=G13)))

➤ Press Enter & the function will return as 4.

sumproduct with multiple columns or logic criteria


3. Using SUMPRODUCT along Multiple Columns & Rows

SUMPRODUCT is one of the most useful functions that can extract data or do complex calculations by scrutinizing all mentioned criteria along rows & columns from an array. In our following & new dataset, the sales of computer devices of different brands are present based on the months. We’ll find out the number of sales for HP notebooks in January.

📌 Steps:

➤ In Cell L11, we have to type:

=SUMPRODUCT(D5:I14,(C5:C14=C10)*(D4:I4=L9)*(B5:B14=L7))

➤ Press Enter & you’ll get the total counts instantly under our criteria.

sumproduct with multiple columns and rows

If you look carefully inside the function, we’ve selected the entire array of sales data first. Then we’ve added the criteria along 2 columns & a row. SUMPRODUCT function has then looked for our criteria in the selected array & returned with the final result by summing up all extracted data.


4. Inserting SUM Function inside SUMPRODUCT to Calculate Weighted Average

By combining SUMPRODUCT & SUM functions together, we can determine the weighted values for a range of data. The following dataset represents a student’s final exam marks along with the assigned weights of a number of subjects. We’ll calculate the weighted average marks of the student.

📌 Steps:

➤ The formula to find the weighted average in Cell F8 will be:

=SUMPRODUCT(C5:C11*D5:D11)/SUM(D5:D11)

➤ Press Enter & you’ll get the weighted average marks at once.

sumproduct with multiple columns to find weighted average

So here SUMPRODUCT function sums up all the products of marks & assigned weights. Then the calculated result will be divided by the summation of all assigned weight percentages.


Concluding Words

I hope, this article on the uses of SUMPRODUCT function along multiple columns will now prompt you to apply in your regular Excel chores. If you got any questions or suggestions let me know through comments. You can check out our other interesting articles related to Excel functions on this website.


You May Also Like to Explore

Nehad Ulfat

Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

1 Comment
  1. Hi, thank you for your help. Thought you might like to know that your formula
    =SUMPRODUCT(D5:I14,(C5:C14=C10)*(D4:I4=L9)*(B5:B14=L7))
    should be;
    =SUMPRODUCT(D5:I14,(C5:C14=L8)*(D4:I4=L9)*(B5:B14=L7))

Leave a reply

ExcelDemy
Logo