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.

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*.

So, our two criteria will be the products â€“ *Apples *and *Oranges*. And we are set to find the total amount matching the 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 (+).

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.

And press **F9**

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.

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

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.

### 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.

So our criteria will be *Apple *and *New York*.

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**).

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.

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.

### 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.

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.

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)Â Â Â Â Â `

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

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.