The Excel SUMIF() function is an incredibly flexible function, letting you sum values if they meet a condition. One thing that’s not clear, is how to sum cells that meet multiple different conditions when any of them should be included. In this tutorial, you’ll learn how to use the Excel SUMIF() function using OR logic.
By the end of this tutorial, you’ll have learned the following:
- How to use the SUMIF() function with OR logic
- How to use the SUMIF() function with dynamic arrays
Understanding the SUMIF() and SUMIFS() Function in Excel
The Excel SUMIF() function allows you to add values that meet a single condition, while the Excel SUMIFS() function lets you add values based on multiple conditions. One important thing to understand, however, is that the SUMIFS() function uses AND criteria. This means that all of the conditions need to be met for the values to be added. Let’s take a look a look at the SUMIF() function:
=SUMIF(range, criteria)As you can see from the code block above, the function takes two arguments. Let’s take a closer look at them in the table below:
| Argument | Description | Required? |
|---|---|---|
range= | The range of cells to apply the condition to | Yes |
criteria= | The condition to apply, including any of the logical operators that may be required | Yes |
The Excel SUMIFS() function works in a similar way, though you can append multiple ranges and criteria following the first set. One important thing to note is that the SUMIFS() function uses AND logic, ensuring that all of the conditions are met.
But, how can you use the Excel SUMIF() function with OR criteria? This means that any of the conditions that you pass in are met. In order to do this, we have two different options:
- Add multiple SUMIF() functions together, or
- Use dynamic arrays to create OR logic
Let’s take a look at the first example by adding multiple SUMIF() functions together.
How to Use SUMIF() With OR Logic
To use the Excel SUMIF() function with OR logic, we can simply use the function multiple times and add the values together. This allows us to combine multiple conditions, even if they’re mutually exclusive.
This is very different than passing in both conditions into the SUMIFS() function. Doing this attempts to add cells where both conditions are included, using AND logic.
Let’s take a look at an example of how we can use the SUMIF() function multiple times to use OR logic. We’ll use different conditions in each of the SUMIF() functions to emulate creating OR logic.
In the example above, we used the Excel SUMIF() function to add cells where the region was either North or South. In order to do this, we use the SUMIF() function twice – once for cells that are equal to North and once for cells that are equal to South. We then add these two sums together.
You can chain this as many times as you like, in order to sum values that meet multiple conditions using OR logic. In order to simplify this, we can also use dynamic array functions. This is what you’ll learn in the following section.
How to Use SUMIF() With OR Logic Using Dynamic Arrays
Dynamic array functions allow for you to simplify your code and make it more dynamic at the same time. We can replicate the SUMIF() function with OR logic example above, by passing an array of values into the SUMIF() function. We create these arrays by passing the values into curly braces. If we wanted to add cells where the region was equal to North or South, we could write {"North", "South"}.
Let’s see how we can use array functions to calculate SUMIF() with OR logic. Use the formula below:
=SUMIF(B3:B13, {"North", "South"})Let’s see what happens when we use this function in Excel to add cells meeting multiple conditions with OR logic:
We can see that by using the function, Excel returns two cells of data. In this case, the values of $7,900 and $5,500 are returned. The values are wrapped in a blue border, meaning that they are part of a spill-over array. While this does tell us the sum that meet the different conditions, it doesn’t provide a single value.
Let’s see how we can wrap the SUMIF() function in the SUM() function to provide the sum of all cells that meet any of the conditions.
In the example above, we wrapped the function in the SUM() function, using the formula below:
=SUM(SUMIF(B3:B13, {"North","South"}))By wrapping the SUMIF() function in the SUM() function, the function returns only a single value. This value represents the sum of all the cells meeting the given conditions, using OR logic.
How to Use SUMIFS() With OR Logic Using Dynamic Arrays
We can go even further to use the Excel SUMIFS() function with OR logic. This means that we can sum values that meet multiple OR conditions. Let’s add another column to our dataset that includes the status of a transaction. The values will include the options of Open, Pending, and Closed. We want to add any values where the status is Open or Pending and the region is either North or South.
In order to do this, we can pass two sets of criteria into the SUMIFS() function. Let’s see what this formula will look like:
=SUMIFS(D3:D13, C3:C13, {"Open","Pending"}, B3:B13, {"North";"South"})When we do this, we return the following values:
Note how the spill-over array that is returned is two-dimensional. This is because our second condition uses a semi-colon. Without this, the function wouldn’t work. It’s important to note as well that the SUMIFS() function uses AND logic between each of the criteria. This means that the logic will be to add values that are (North OR South) AND (Open OR Closed). This means that cells that are West and Open won’t be included.
We can still easily wrap this function in the Excel SUM() function in order to return the sum of values with multiple OR conditions.
We can see that by wrapping the function is the SUM() function, a single, conditional sum is returned! This combined multiple OR conditions, allowing us to build a very flexible function.
Conclusion
In this tutorial, you learned how to use the Excel SUMIF() function and SUMIFS() function with OR logic. This means that you can sum values that meet any of the conditions that you pass in. By default, the SUMIFS() function will use AND logic, meaning that the function adds only cells that meet all of the conditions.
You first learned how to add cells meeting any condition by adding multiple SUMIF() functions together. Then, you learned how to use dynamic arrays to use OR logic with the SUMIF() function, by wrapping the result in the SUM() function. Finally, you learned how to combine multiple OR conditions using the SUMIFS() function with dynamic arrays.
Additional Resources
To learn more about related topics, check out the tutorials below: