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