Method 1 – Using SUMPRODUCT with Multiple Columns Under AND Logic Criteria
Steps:
- Select cell G15, and type the following formula:
=SUMPRODUCT((B5:B21=G12)*(C5:C21=G13)*(D5:D21))
- Press ENTER, and you’ll get the result at once.
How the Formula Works
B5:B21=G12 matches cell G12 (i.e. Lenovo) with the range B5:B21 & C5:C27=G13 also matches cell G13 with the range C5:C27 and returns TRUE or FALSE. Matching both ranges, the total price of Notebook device of the Lenovo brand is calculated inside the function.
With the SUMPRODUCT function, extract the total counts of Lenovo notebooks or any other category from the table.
Steps:
- Select cell G18, and type:
=SUMPRODUCT(--(B5:B27=G12),--(C5:C27=G13))
- Press ENTER, and you’ll see the result right away.
Formula Explanation
In this case, we’re using logic arguments inside the SUMPRODUCT function. We’re asking the function to determine if the selected criteria match the cell values in Columns B & C. Uses of Double-Unary(–) before the arguments convert the logical text values (TRUE or FALSE) into 1 or 0. The SUMPRODUCT will sum up those number values to display the final result.
Method 2 – Using SUMPRODUCT with Multiple Columns Under OR Logic Criteria
Steps:
- Type the formula below in cell G15.
=SUMPRODUCT((B5:B21=G11)*((C5:C21=G12)+(C5:C21=G13))*(D5:D21))
- Press ENTER, and the total sales price will be displayed in that cell.
How the Formula Works
B5:B21=G11 matches cell G11 (i.e. Lenovo) with the range B5:B21 & C5:C27=G12 matches cell G12 with the range C5:C27 & D5:D27=G13 matches cell G13 with the range D5:D27 and returns TRUE or FALSE. Matching both ranges, the total price of the Notebook and Desktop of the Lenovo brand is calculated inside the function.
If we want to count the presence of Lenovo devices in the table, we will have to add the device criteria by inserting a Plus(+) between those two criteria.
Apply the formula below in cell G18.
=SUMPRODUCT(--(B5:B27=G11),--((C5:C27=G12)+(C5:C27=G13)))
Method 3 – Using SUMPRODUCT Along Multiple Columns and 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 presently based on the months. We’ll find out the number of sales for HP notebooks in January.
The SUMPRODUCT function can extract data or perform complex calculations by scrutinizing all mentioned criteria along rows and columns from an array. In our following and new dataset, the sales of computer devices of different brands are currently based on months. We’ll find out the number of sales for HP notebooks in January.
Apply the following formula in cell L11 and you’ll get the total counts instantly under our criteria.
=SUMPRODUCT(D5:I14,(C5:C14=C10)*(D4:I4=L9)*(B5:B14=L7))
Formula Explanation
If you look carefully inside the function, we’ve selected the entire array of sales data first. Then we added the criteria along 2 columns and a row. SUMPRODUCT function then looked for our criteria in the selected array and returned the final result by summing up all extracted data.
Method 4 – Inserting SUM Function Inside SUMPRODUCT to Calculate Weighted Average
By combining the SUMPRODUCT and SUM functions, 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 student’s weighted average marks.
The formula to find the weighted average in cell F8 will be:
=SUMPRODUCT(C5:C11*D5:D11)/SUM(D5:D11)
The SUMPRODUCT function sums up all the products of marks & assigned weights. The calculated result will be divided by the summation of all assigned weight percentages.
Download Practice Workbook
You can download our Excel workbook that we’ve used to prepare this article.
Related Articles
- SUMPRODUCT Across Multiple Sheets in Excel
- SUMPRODUCT for Counting with Multiple Criteria in Excel
- [Solved] SUMPRODUCT with Multiple Criteria Not Working in Excel
- Excel SUMPRODUCT with Multiple Criteria in Same Column
<< Go Back to Excel SUMPRODUCT Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
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))
Hello, MARK EVANS!
You have pointed out a fantastic thing.
Your reference is correct. We had an unfortunate mistake in the reference to this formula. There will be L8 at the reference in the place of the C10 reference.
Thank you for your valuable feedback. We appreciate it so much.
So, the formula should be:
=SUMPRODUCT(D5:I14,(C5:C14=L8)*(D4:I4=L9)*(B5:B14=L7))Regards,
Tanjim Reza
This was LIFE CHANGING. I thought I was good at Excel til I saw this. My god!
Hi,
regarding:
1. Using SUMPRODUCT with Multiple Columns Under AND Logic Criteria
what if I want to see not 1 brand, but 2 or 3 brands?
Thank you in advance!
Hello ALEQ,
Thank you for your query. If you want to sum one specific product of two or three brands you just have to use an OR logic for brands.
Let us assume we want to find the sum of notebook sales of Lenovo and Asus brands. For that insert the following formula:
=SUMPRODUCT(((B5:B21=G12)+(B5:B21=G13))*(C5:C21=G14)*(D5:D21))
Here, (B5:B21=G12)+(B5:B21=G13), this part applies the OR logic for brands. It matches the range B5:B21 with G12 and G13 cell values and returns TRUE or FALSE. So, it returns TRUE for Lenovo and Asus brands and Excel counts them as 1.
Similarly, C5:C21=G14 returns TRUE or 1 for if any match is found and D5:D21 simply returns the sales values.
So, the function sums up the prices of notebooks of two different brands.
If you want to sum up prices for more brands, then just add another logic that matches brands in this part “(B5:B21=G12)+(B5:B21=G13)” of the formula.
Regards,
Priti
Exceldemy Team
How would I add months together like JAN & FEB in the formula but keeping the data range the same.
Hi TRICIA,
Thank you for your comment. To add the sales of JAN & FEB you can use this formula:
=SUMPRODUCT(D5:I14,(C5:C14=C10)*(D4:I4=L9)*(B5:B14=L7))+SUMPRODUCT(D5:I14,(C5:C14=C10)*(D4:I4=L10)*(B5:B14=L7))Regards,
Mahfuza Anika Era
Exceldemy Team