## SUMIFS with OR

Of all the functions introduced in Excel 2007, 2010, and 2013, my personal favorite is SUMIFS. The SUMIFS function performs multiple condition summing. The function is designed with AND logic, but, there are several techniques that allow us to use OR logic instead. This post explores a few of them.

*Note: if your version of Excel has the FILTER function, check out this post as well.*

## Objective

Let’s be clear about our objective by taking a look at a worksheet. Let’s assume we are trying to generate a little report based on data exported from an accounting system. We would like to write formulas to populate the report shown below.

We want to populate the report by aggregating the values from the exported accounting data, shown below.

Since the SUMIFS function is designed with AND logic, the following formula wouldn’t work to populate the report’s sales value:

=SUMIFS($C$18:$C$28, $B$18:$B$28,"Sales-Labor", $B$18:$B$28,"Sales-Hardware", $B$18:$B$28,"Sales-Software")

The formula wouldn’t work because there are no rows where the account is equal to Sales-Labor AND equal to Sales-Hardware AND equal to Sales-Software. As a result, the formula above returns zero.

Now that we have a clear idea of the situation, let’s work through several approaches.

## SUMIFS and Wildcard

If we are lucky, there is a uniform pattern in the labels that we can use with wildcards. For example, in the data above, all of the sales accounts happen to start with “Sales-” and that makes us really lucky. We can simply use a wildcard to tell the SUMIFS function to add up any rows where the account begins with Sales- as shown in the formula below:

=SUMIFS($C$18:$C$28,$B$18:$B$28,"Sales-*")

Here it is in the report:

*Note: Alternatively, we could reference the report label in the cell, and update the third argument to B6&”*”*

A similar function could populate the COS report value, as follows:

=SUMIFS($C$18:$C$28,$B$18:$B$28,"COS-*")

And then we could populate the SG&A report value by adding up all of the accounts that do not have a dash, as follows:

=SUMIFS($C$18:$C$28,$B$18:$B$28,"<>*-*")

However, in practice, we are not often lucky enough to have such a pattern. So, let’s explore other options.

## SUMIFS+SUMIFS

Another option is to simply string together a series of SUMIFS functions, each one designed to aggregate a specific label. For example, we could use SUMIFS to add up all rows where the account is equal to Sales-Labor, and then add to its result a SUMIFS function designed to add up all rows where account is equal to Sales-Hardware, and so on. This idea can be illustrated as follows:

=SUMIFS()+SUMIFS()+SUMIFS()

The corresponding formula to populate the sales report value is shown below.

=SUMIFS($C$18:$C$28,$B$18:$B$28,"Sales-Labor")+ SUMIFS($C$18:$C$28,$B$18:$B$28,"Sales-Hardware")+ SUMIFS($C$18:$C$28,$B$18:$B$28,"Sales-Software")

When there are only a couple of criteria values, this approach may get the job done. When there are several criteria values, we can simplify the formula and remove the redundancy by using an array criteria value argument. Let’s examine this approach now.

## SUM(SUMIFS({}))

We can create a list of criteria values and include them in a single SUMIFS argument. To create a list like this, technically called an array, we simply surround it with {curly brackets}. There is one little trick to get this approach to work, but, let’s take it one step at a time.

First, let’s take a look at a formula that includes the account list in an array argument:

=SUMIFS($C$18:$C$28,$B$18:$B$28,{"Sales-Labor","Sales-Hardware","Sales-Software"})

You’ll notice that we used a SUMIFS function with a single criteria value argument. The criteria value argument is a comma-separated list surrounded by {curly braces}. This essentially tell’s the function to return the amounts for each of the listed accounts.

However, when we hit Enter, we notice that the formula only returns the sum of the first account, Sales-Labor. This brings us to our little trick. The array argument causes the formula to return three results, and since we wrote the formula in a single cell, we only see the first result. To have our cell show the sum of all three results, we simply enclose the SUMIFS function in a SUM function, as follows:

=SUM(SUMIFS($C$18:$C$28, $B$18:$B$28,{"Sales-Labor","Sales-Hardware","Sales-Software"}))

*Note: if you are familiar with array formulas, you can select three cells, then hit Ctrl+Enter to view the three results in three cells.*

While each of the approaches above may achieve our objective, there is another approach that may work better for recurring-use workbooks. The concern with all of the options above is that they store the criteria values, the account names, in the cells as text strings. This prevents us from using consistent formulas within the report range. Whenever possible, we try to write consistent formulas within a range that can be filled down. We are unable to fill the above formulas because the criteria values are embedded in them. Since this makes the worksheet more difficult to maintain over time, it is often preferred to store the criteria values in cells and then reference the cells in the SUMIFS function. We can accomplish this by using a simple report group lookup table.

## Report Group Lookup

Before we dig into the mechanics, let’s take a step back for a moment. Essentially, we are trying to group transaction values for reporting purposes, but, the group labels do not appear in the data. Had the accounting system exported the report group labels, our job would have been easy. The good news is that even though it didn’t, we can easily define the report groups with a simple lookup table.

Consider the following lookup table which defines the desired report groups.

We can easily add a helper column to the data table that retrieves the proper report group. We can use our favorite lookup function to accomplish this task. For example, the following formula retrieves the report group with a VLOOKUP function:

=VLOOKUP(B18,$B$34:$C$44,2,0)

When we fill the formula down, the updated data table includes the report group, as shown below.

Now, we can populate the report’s sales value with a simple formula.

=SUMIFS($C$18:$C$28,$D$18:$D$28,B6)

We can fill this formula down throughout the report as shown below.

The consistent formulas make us very happy and the report group column makes it clear how the values flow into the report.

If you have any other techniques for using OR logic with SUMIFS, please share by posting a comment below…thanks!

## Additional Resources

- Download sample file: SUMIFS with OR
- The SUMPRODUCT function can also be used to perform conditional summing with OR logic
- If you needed to use OR logic for numerous column, instead of within a single column as discussed above, be careful not to double-count rows that meet both criteria
- Excel University Volume 2 explores the SUMIFS and VLOOKUP functions in detail

### Excel is not what it used to be.

You need the **Excel Proficiency Roadmap** now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

### Want to learn Excel?

Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office hours.

Great info, thank you!

This is awesome! Thanks.

Instead of below formulas we can use

=SUM(SUMIFS($C$18:$C$28,$B$18:$B$28,{“Sales-Labor”,”Sales-Hardware”,”Sales-Software”}))

2nd Option

=SUMPRODUCT(SUMIFS($C$18:$C$28,$B$18:$B$28,I9:I11))

Where I9:I11 is a range where we have written I9=”Sales-Labor”, I10=”Sales-Hardware”,I11=”Sales-Software”

So we can change value in range

Thanks!

u are welcome

Muhammads comment saved me a lot of time – I have been strugling a long time to use sumifs with array in VBA and this solved the problem

Thanks a lot!

I do love it. Amazing. I also found a similar way to do.

Nice.

I tried to use this with two colums with multiple values.

One column (B) can be filled with AA, BB, or CC and the other column (D) can be filled with XX or YY.

e.g. SUM(SUMIFS($C$18:$C$28,$B$18:$B$28,{“AA”,”BB”,”CC”},$D$18:$D$28,{“XX”,”YY”}))

This doesn’t seem to work. Anybody an idea?

Tnx

Hey Rene,

I’m sorry, but I can’t seem to replicate your situation…I recommend looking into SUMPRODUCT() as well, though.

I hope you are able to find a solution! Let me know if you do

KurLeBlanc

I’m preparing for an interview with an Accounting firm (corporate finance and advisory department). Heading for an interview in an hour told i’ll be given an excel spreadsheet to produce management accounts.

I just spent the whole day on your website. Visited all your udemy courses and been sleeping on this website for weeks now. Your approach is great. So easy to understand and everything makes sense to me now.

Thanks lots for these…

Best of luck on your interview!

Muhammad’s 2nd formula is amazing, this saved me sooo much time. Thankyou so much for this!

u are welcome

Thanks Muhammad, really a great solution.

Thanks Muhammad

Hi,

I’m hoping someone can help me out. This website does a great job explaining how to utilize these functions, but I am wondering if it can be taken a step further. One limitation I have had with the curly brackets within the SUM(SUMIFS formula is that I cannot do any kind of cell reference within the brackets. I work with large sets of data where the criteria within the brackets could differ from one row to the next. Is it possible to do cell references within the brackets? Or if someone has any other suggestions I would greatly appreciate it. Below is a sample of data and how I would need to summarize the earnings per person:

Data

Cat Name Earnings

1 David 300

1 Michael 100

1 Travis 200

5 David 500

5 Michael 700

5 Travis 300

6 David 50

6 Michael 200

6 Travis 500

7 David 150

7 Michael 325

7 Travis 475

Summary

Name $ Categories to include in sum

David 1,5

Michael 1,5,6,7

Travis 5,6,7

So the way I would have to do the formula for Michael would be:

SUM(SUMIFS(Data!C:C,Data!B:B,Summary!A2,Data!A:A,{1,5,6,7}))

and the criteria within the brackets for each person would be different. This is easy to do for just a few people, however in my actual data I could I thousands of people and the categories to include for each person could be any combination of up to 23 categories. Any ideas how to create a universal formula? A solution to this issue could save me a massive amount of time.

Hi I want to put multiple conditions of equal to and not equal to as well

Used the below formula but what happens is the Sum is doubled . When i checked on evaluate formula it showed Sum(100,100) instead of Sum(100)

=SUM(SUMIFS($F:$F,$C:$C,”C131″,G:G,”0″,D:D,”CPRTY”,D:D,”RPRTY”))

1) Sum of $F:$F, for C131 in $C:$C

2) 0 in G:G

3) CPRTY and RPRTY in D:D

PS: The formula worked correctly for equal to conditions..

Please advise

Hi Martha!

Your formula looks correct until you want the function to use OR logic for the last condition. I believe this article will really help: http://www.excel-university.com/sumifs-with-or/

Let me know if that works:)

Kurt LeBlanc

Hi all,

I know this is an old post, however i was hoping someone could assist me. I am attempting to use more than one range in Muhammad’s formula. Is this possible?

Thanks very much.

I am also trying to do what I believe Mark Francis is asking about – I got Muhammad’s formula to work well when I have one criteria with an “OR” in the function. When I tried to make two criteria have an “OR” option, it no longer worked. Is there a way to do this? I don’t want to use an array and already have enough helper columns going on, so I was hoping the formula could be tweaked to accomplish my objective using a regular formula. Thanks!