While SUMIF and SUMIFS are great choices when working with conditional summing formulas, they fall short in one area. Specifically, they can’t be used to sum with multiple criteria on the same column. To be clear, this is specifically when you are trying to use the multiple criteria as an **OR** operation rather than an **AND** operation. When SUMIFS is used with multiple criteria, it’s like saying “If column A has this AND this, then sum.” But if you need something like “if column A has this OR this, then sum” then, well, you’re gonna have a bad time.

# SUM with Multiple Criteria UsingĀ OR

Let’s say you had a simple table like the following:

(This is an Excel Table and it’s called Sales)

If you wanted to sum the sales based on an OR condition like “sum the sales if the region is East US **OR** West US,” how would you do that?

You may think to use `SUMIFS()`

for this, but `SUMIFS()`

apply multiple criteria with an **AND** operation:

`=SUMIFS(Sales[Sales (USD)],Sales[Region],"East US",Sales[Region],"West US")`

The problem here is that we’re trying to apply multiple criteria to the **same column**, which will be applied like an AND operation for SUMIFS(). The SUMIFS() function in this case says:

Sum the sales if the region is “East US” AND if the Region is “West US”SUMIFS() function

This could never be true so the equation evaluates to 0.

That’s not to say that `SUMIFS()`

isn’t useful. You can apply multiple criteria to the same column so long as the logic makes sense. For example you could do this:

`=SUMIFS(Sales[Sales (USD)],Sales[Sales (USD)],">12000",Sales[Sales (USD)],"<15000")`

This would sum up the sales only if they were between $12,000 and $15,000.

So how can we apply multiple criteria to the same column with an **OR** logical condition?

## Use the plain old SUM() function

Poor `SUM()`

function, it doesn’t seem to have the popularity that it deserves. `SUM()`

can do everything that `SUMPRODUCT`

, `SUMIF`

, `SUMIFS`

, `COUNT`

, `COUNTIF`

, and `COUNTIFS`

can do and more, but my guess is that people tend to find the syntax confusing and avoid trying to use it altogether.

I’m going to write another post in the future explaining how awesome the `SUM()`

function really is, but for now we’ll keep the conversation on using it to replace `SUMIFS()`

.

To sum the sales if the region was East US OR West US, we can use the sum function like this:

`=SUM((Sales[Region]={"East US","West US"})*(Sales[Sales (USD)]))`

Please note that this is an Array Formula and must be entered with Ctrl+Shift+Enter (Command+Shift+Enter on Mac). For more info about array formulas, click here.

### Breaking down the formula

There are two parts to this formula:

`=SUM((Sales[Region]={"East US","West US"})*(Sales[Sales (USD)]))`

In the **red section**, we have the multiple conditions that we will apply. In the **blue section**, we will match up the conditions with the corresponding sales numbers so we know which ones to sum up.

Let’s evaluate the conditions first:

`=SUM((Sales[Region]={"East US","West US"})*(Sales[Sales (USD)]))`

The first thing that will happen is that the `Sales[Region]`

will be shown as an array of values:

`=SUM((`

**{"East US","West US","West Europe"}**={"East US","West US"})*(Sales[Sales (USD)]))

When we match up the values on the left with the values on the right, we get an array of boolean values:

`=SUM((`

**{TRUE,TRUE,FALSE}**)*(Sales[Sales (USD)]))

This is because “East US” on the left was found in the right (TRUE), the “West US” on the left was found in the right (TRUE), but “West Europe” was not found in the right side of the equation (FALSE).

Now let’s look at the `Sales[Sales (USD)]`

side of the formula:

`=SUM(({TRUE,TRUE,FALSE})*(`

**{14000,13000,15000}**))

This just ends up showing the values from the `Sales USD`

column in the table.

Now all we have to do is sum up what’s TRUE:

`=SUM(({`

**TRUE**,**TRUE**,FALSE})*({**14000**,**13000**,15000}))

`=SUM(14000,13000)`

`=27000`

And that’s how you can use `SUM`

to apply multiple criteria on the same column.