SUMPRODUCT Multiple Criteria in Excel(3 Approaches)

Circumstances may demand you to work with multiple criteria within the SUMPRODUCT function. To assist you then, today we are going to show you how to use SUMPRODUCT with multiple criteria. For this session, we are using Excel 2019, feel free to use yours.

Read more: SUMPRODUCT with Criteria in Excel

First things first, let’s get to know about the workbook which is the base of our examples.

Dataset - SUMPRODUCT Multiple Criteria

Here we have a dataset for a few products with suppliers and their respective cities. Using this table we will show how we can use SUMPRODUCT with multiple criteria.

Note that this is a basic table with dummy data to keep things straightforward. In a real-life scenario, you may encounter a much larger and complex dataset.

Practice Workbook

You are welcome to download the practice workbook from the link below.

SUMPRODUCT with Multiple Criteria

1. Dealing Criteria within 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.

For example, we will find the total sales amount for product Apples or Oranges.

OR logic example - SUMPRODUCT Multiple Criteria

So, our two criteria will be the products – Apples and Oranges. And we are set to find the total amount matching the criteria.

OR logic example criteria

Our OR logic operation can be done using a single SUMPRODUCT function or multiple SUMPRODUCT functions. For a reminder, you can visit the article SUMPRODUCT.

I. Use Single SUMPRODUCT

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.

The formula will be the following one

=SUMPRODUCT((($D$4:$D$23=$H$4)+($D$4:$D$23=$I$4)),E4:E23)

Here we have separated the two criteria check operation by the plus symbol (+).

OR Logic formula - SUMPRODUCT Multiple Criteria

Here we have checked for two criteria (for simplicity). If the criteria increase, write them using the + sign.

This portion returns the array of 0s and 1s when the criteria are checked.

To check this, select that portion.

Insights - SUMPRODUCT Multiple Criteria

And press F9

Insights results - SUMPRODUCT Multiple Criteria

Here we found 1 when there are either Apples or Oranges. And E4:E23 is the array of sales. 

The SUMPRODUCT function then multiplies the arrays and sums them.

OR logic result - SUMPRODUCT Multiple Criteria

Here we have found the total amount for these products.

II. Use Multiple SUMPRODUCT

While dealing with OR logic we can use multiple SUMPRODUCT functions as well. The methodology will be similar to the earlier one, the only change will be, separate the functions by a plus symbol (+).

The formula will be

=SUMPRODUCT(--($D$4:$D$23=$H$4),E4:E23)+SUMPRODUCT(--($D$4:$D$23=$I$4),E4:E23)

Multiple SUMPRODUCT in OR logic - SUMPRODUCT Multiple Criteria

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.

Multiple SUMPRODUCT in OR logic result

2. SUMPRODUCT with AND Logic

In the previous section, we have got the result while SUMPRODUCT behaves like 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. Let’s explore how AND operation can be done using SUMPRODUCT.

For example, we will find the total amount of sales of Apples from New York city.

AND logic example - Multiple SUMPRODUCT in OR logic - SUMPRODUCT Multiple Criteria

So our criteria will be Apple and New York.

AND logic example criteria - Multiple SUMPRODUCT in OR logic

As we are going to use the asterisk sign (*) between two criteria validation. This sign operates like AND logic.

Now, let’s see the formula

=SUMPRODUCT((($D$4:$D$23=$H$4)*($B$4:$B$23=$H$5)),E4:E23)

Here the criteria values are in two different columns. So we have checked the criteria for columns Product (D4:D23) and City (B4:B23).

Multiple SUMPRODUCT in OR logic

These logics that checks criteria: $D$4:$D$23=$H$4 and $B$4:$B$23=$H$5, return arrays with TRUE or FALSE. Then these two get multiplied (in arithmetic operation an asterisk sign acts as a multiplication operator).

Remember when only two TRUE values get multiplied the result becomes TRUE and in all other cases, it is FALSE.

Then the SUMPRODUCT function gets two arrays, it multiplies the elements of the arrays and returns the result.

AND logic formula result

Here we have found the total selling amount for Apples in New York city.

You can use the unary operators ahead of the logic check operations.

 =SUMPRODUCT((--($D$4:$D$23=$H$4)*(--($B$4:$B$23=$H$5))),E4:E23)

The formula works similarly to the earlier formula, only it calculates converting the TRUE and FALSE into 1s and 0s.

But the final result will be the same.

Alternative AND logic formula result

3. Combination of AND – OR Logic Operation

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 the logic. Here, for example, we are going to count the total sales of Apples from New York or Los Angeles.

AND - OR Logic example

This means we will count the sales amount from New York or Los Angeles city but only for the product Apples. So, the criteria will be Product Apples and City New York and Los Angeles.

AND - OR Logic example criteria

So far, we have known that + (plus sign) and * (asterisk sign) for OR and AND logic respectively.

We will write the formula in such a way that the city values checking can be there within OR, and then that logic checks, and the product name will be within AND.

 Let’s see the formula

 =SUMPRODUCT((($D$4:$D$23=$H$4)*(($B$4:$B$23=$H$5)+($B$4:$B$23=I5))),E4:E23)     
AND - OR Logic formula

Here $B$4:$B$23=$H$5 and $B$4:$B$23=I5 check the cities (New York and Los Angeles respectively). These are set within OR logic (separated by +)

And then we combined these two with $D$4:$D$23=$H$4 using AND logic.

Hope you have understood that ultimately these logic checking operations will return an array of TRUE and FALSE.

Then the SUMPRODUCT function performs its operation with this Boolean value array and Sales column array (E4:E23)

AND - OR Logic formula result

Here we have found the total we were looking for.

All the formulas we have used here can be written using the unary operators (--), though it will change the inner complexity the result will remain the same.

Conclusion

That’s all for today. We have listed several approaches to use SUMPRODUCT with multiple criteria. Hope you will find this helpful. Feel free to comment if anything seems difficult to understand. Let us know any other methods which we might have missed here.


Further Readings:

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