Nashid
In this lesson, I’ll demonstrate 5 effective approaches on how to use the SUMPRODUCT function for multiple criteria in the same column including some other problems related to this one. These techniques will enable you to solve the problem that led you here. You will master several crucial Excel features and functions during this article, which will come in extremely handy for any assignment using Excel.
📁 Download Excel File
The sample worksheet that was used during the discussion is available to download for free right here.
Learn to Use SUMPRODUCT for Multiple Criteria in the Same Column with These 5 Methods
Say we have a dataset of Some store that has different data similar to our problem. We will use SUMPRODUCT function to determine these problems as it can be used in different ways with the combination of logics. Follow these approaches the learn SUMPRODUCT functions application for multiple criteria along same column.
1. SUMPRODUCT with Multiple Criteria Comparing Two or More Arrays
SUMPRODUCT is a versatile function. We can do different operations with this function. For instance we have a dataset of different IDs product sale lists against their respective targets. We are going to determine How many IDs couldn’t meet their targets.
⬇️⬇️ STEPS ⬇️⬇️
- First, select the cell to put in the formula.
- After that copy down the following formula or modify it as per your problem.
=SUMPRODUCT((E6:E14<D6:D14)*1)
- Therefore, press Enter for the result.
- In addition, if you want specific answers like how many IDs selling Mouse couldn’t meet their targets try using the following code.
=SUMPRODUCT((E6:E14<D6:D14)*(C6:C14=”Mouse”))
- Press Enter for the result.
2. Application of OR Logic
Utilize plus sign (+) among the arrays to selectively add up or count cells using the OR algorithm. We can use this with SUMPRODUCT. Let’s go through a problem. For instance, let’s say we have the same dataset as above and we want to know the total sale for Mouse and Monitor. Follow the steps below.
⬇️⬇️ STEPS ⬇️⬇️
- First, select a cell and copy down the following formula for the count of the products.
=SUMPRODUCT((C6:C14=”Mouse”)+(C6:C14=”Monitor”))
- Press Enter and copy the following formula for determining the Total sale amount of those two products.
=SUMPRODUCT((C6:C14=”Mouse”)+(C6:C14=”Monitor”), E6:E14)
- Now again pressing Enter we can see the result.
3. Using AND Logic in SUMPRODUCT Function
We can use the SUMPRODUCT function and employ AND algorithm to solve certain problems. Let’s say we have a data set like the previous problem. Now we will try to determine two specific criteria. We will find how many sales ID G acquired by selling Mouse.
⬇️⬇️ STEPS ⬇️⬇️
- First, select the cell and apply the following formula.
=SUMPRODUCT((B6:B14=”G”)*(C6:C14=”Mouse”)*E6:E14)
- After that press Enter to view the result.
4. Using Both AND and OR Logic
The AND operator is represented by an asterisk (*) and the OR operator is represented by the plus sign (+). We will use both the logic using the SUMPRODUCT function to determine the total sale of Keyboards and Mouse from Razer company. See the instructions below.
⬇️⬇️ STEPS ⬇️⬇️
- First, select a cell for the input and copy the following formula or modify it as you need.
=SUMPRODUCT((B6:B14=”Razer”)*((C6:C14=”Keyboard”)+(C6:C14=”Mouse”))*D6:D14)
- Now Press Enter for results.
5. SUMPRODUCT Formula for Weighted Average
Excel’s SUMPRODUCT is frequently used to compute weighted averages, wherein every number is given a specific weight. Here we will be demonstrating this.
⬇️⬇️ STEPS ⬇️⬇️
- First, choose a cell and copy the following formula.
=SUMPRODUCT(C6:C14,D6:D14)/SUM(D6:D14)
🔨 Formula Breakdown
👉 SUMPRODUCT(C6:C14,D6:D14)
This part selects C6:C14 which are the values of column C and multiplies it with the data from column D which are D6:D14 then it SUMs them up.
👉 SUM(D6:D14)
This portion of the formula sums the total column D6:D14 which for our case would be 37.
👉 SUMPRODUCT(C6:C14,D6:D14)/SUM(D6:D14)
This will divide the SUMPRODUCT of column C and D that means the multiplied summation, by the SUM of Column D, which gives us the Weighted Average.
- This formula will determine the weighted average of our data. So press Enter for the result.
📄 Important Notes
🖊️ Be careful while using the code as there are a lot of terms and if you lose concentration the code may be incorrect.
🖊️ Use shortcuts to reduce time.
🖊️ Change the formats as you want them before applying a formula.
🖊️ While writing formulas carefully see the suggestions and don’t forget the commas and parentheses.
📝 Takeaways from This Article
📌 Use of the SUMPRODUCT function.
📌 Different approaches can be used using just one function.
📌 How to use AND as well as OR logic in a function using * and + respectively.
Conclusion
I hope you were able to use the techniques I demonstrated in this Excel lesson to use the SUMPRODUCT function for multiple criteria in the same column. As you can see, there are a lot of options on how to do this. Decide deliberately on the approach that best addresses your circumstance. I advise repeating the steps if you become confused in any of the steps if you get stuck. Practice on your own after taking a look at the Sumproduct Multiple Criteria Same Column Excel file in the practice workbook as I’ve provided above because practice makes a man perfect. I earnestly hope you can use it. Please share your experience with us in the comment box about how your experience was throughout the article and what we need to improve or add to make things better for you folks.
Again if you have any problem regarding Excel spit it in the comment box. The Excelden crew is always available to answer your questions. Keep educating yourself by reading. For more Excel-related articles please visit our website Excelden.com.
