Shakil Ahmed
In order to work with Microsoft Excel, we face different critical situations regarding different criteria. Circumstances may demand you to work with multiple criteria within the SUMPRODUCT function. Today we are going to show you 3 smart approaches to how to use SUMPRODUCT with multiple criteria in Excel. I hope it will be helpful for you if you are looking for a similar sort of thing.
Download Practice Workbook
3 Smart Approaches of Using SUMPRODUCT with Multiple Criteria in Excel
With a view to explaining the ways to use SUMPRODUCT with multiple criteria, we are going to use a dataset mentioned below. In this dataset, we have arranged the products coming from different cities in the City, Supplier, Product, and Sales columns.
1. Apply SUMPRODUCT for OR Logic
While dealing with multiple criteria within SUMPRODUCT, we may either need to operate through OR logic or AND logic. In this section, we will deal with multiple criteria in OR logic operation. You might have known that ‘+‘ works as OR logic. So for this, we will use ‘+‘ to operate. We will use the plus symbol (+) in between the array_criteria. Again, we can both single or multiple SUMPRODUCT to fulfill our expectations.
1.1 Single SUMPRODUCT Functions
In the case of finding the total amount of two products among many, we can use the single SUMPRODUCT function. According to the dataset, we will try to find the total sales value of Apples and Oranges. Follow the following steps to execute the purpose.
Steps:
- Pick your criteria based on what you want to have the output. Here, I have chosen Apples and Oranges as the criteria to find the total amount.
- Next, choose a cell (i.e. H7) to input the formula to have the desired output.
- Now, input the following formula in that cell.
=SUMPRODUCT((($D$5:$D$24=$H$5)+($D$5:$D$24=$H$6)),E5:E24)
- Finally, press ENTER to have the result.
1.2 Multiple SUMPRODUCT Functions
We can also use multiple SUMPRODUCT to do the same task. Just see the procedures mentioned below.
Steps:
- Pick a cell and input the following formula to find the total sales value of Apples and Oranges.
=SUMPRODUCT(--($D$5:$D$24=$H$5),E5:E24)+SUMPRODUCT(--($D$5:$D$24=$H$6),E5:E24)
Here, we have slotted each criterion within each of the SUMPRODUCT functions. You can see ahead of the logic check we have used double unary signs (—).
The logic check operation would have returned TRUE or FALSE but as we have used the unary signs, it will return 1s or 0s.
Then, both SUMPRODUCT functions multiply the resultant arrays to the Sales column and produce the final result.
- Hit ENTER to have the total sales value.
Read More: How to Use SUMPRODUCT with Criteria in Excel (5 Methods)
Similar Readings
- Excel SUMPRODUCT with Multiple Criteria in Same Column
- How to Use SUMPRODUCT to Lookup Multiple Criteria in Excel
- Example with Excel Solver to Minimize Cost
2. Employ SUMPRODUCT with AND Logic
In the previous section, we got the result while SUMPRODUCT behaves like an OR operation, which provides the result once either of the conditions is satisfied. If you need results that satisfy both(all) the criteria, then your desired operation should be AND operation. According to the dataset, we will try to find the total amount of sales of Apples from New York city where we have to satisfy to criteria.
Steps:
- First of all, pick a cell and input the following formula to find the total amount of sales of Apples from New York
=SUMPRODUCT((($D$5:$D$24=$H$5)*(($B$5:$B$24=$H$6))),E5:E24)
- Followingly, press ENTER to have the output.
Read More: [Solved] SUMPRODUCT with Multiple Criteria Not Working in Excel
3. Use SUMPRODUCT with Both AND–OR Logic
You can use both the AND–OR logic together within the SUMPRODUCT while dealing with multiple criteria. There may arise many situations where you need to utilize both logic. For example, we are going to count the total sales of Apples from New York or Los Angeles. This means we will count the sales amount from New York or Los Angeles city but only for the product Apples.
Steps:
- Choose a cell and input the following formula to find the total sales value of Apples from New York or Los Angeles.
=SUMPRODUCT((($D$5:$D$24=$H$5)*(($B$5:$B$24=$H$6)+($B$5:$B$24=$H$7))),E5:E24)
- Finally, hit the ENTER button to have the total sales value of Apples from New York or Los Angeles.
Read More: SUMPRODUCT for Counting with Multiple Criteria in Excel
Conclusion
At the end of this article, I like to add that I have tried to explain 3 smart approaches to how to use SUMPRODUCT with multiple criteria in Excel. It will be a matter of great pleasure for me if this article could help any Excel user even a little. For any further queries, comment below. You can visit our site for more articles about using Excel.
