How to Use SUMPRODUCT with Multiple Criteria in Excel

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.

Sumproduct Multiple Criteria


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.

Single SUMPRODUCT Functions

  • 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)

Multiple SUMPRODUCT Functions

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: SUMPRODUCT with Criteria in Excel


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)

Employ SUMPRODUCT with AND Logic

  • Followingly, press ENTER to have the output.

Sumproduct Multiple Criteria

Read More: [Solved] SUMPRODUCT with Multiple Criteria Not Working in Excel


Similar Readings


3. Use SUMPRODUCT with Both AND–OR Logic

You can use both the ANDOR 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.

Sumproduct Multiple Criteria

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.


Further Readings

Shakil Ahmed

Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo