Aggregate with OR Logic

The family of IFS functions, such as SUMIFS and COUNTIFS, use AND logic when evaluating multiple conditions. In other words, all conditions must be true to be included. And generally, this logic works well. However, there are situations when we’d like to use OR logic instead. That is, any of the conditions can be true to be included.

In this post, we’ll talk about how to aggregate using OR logic. In summary, we’ll determine the values to include with the FILTER function and then wrap the desired aggregation function (SUM, COUNT, AVERAGE, MIN, MAX, …) around it.

Objective

Before we jump into the details, let’s confirm our objective. Let’s say we’ve exported a bunch of data from our sales system and it is stored in a table named Table1:

We have a promotion where some orders qualify for free shipping. The orders that qualify are those where the Date is 12/25 OR the Voucher code is FREE OR the Amount is greater than 100.

We would like to know some basic information about the orders that qualified for free shipping, including how many, the total amount, the largest order, and the list of orders. Basically, results like this:

As this is Excel, there are many ways to try to approach this. For example, we could try conditional formatting. This would help us identify the free shipping orders, however, it would then be a challenge to analyze them using functions such as SUM, MAX, or COUNT.

We could also set up one or more helper columns to look at the conditions, and then reference the results of the helper columns in our aggregation function. But, as a general philosophy, I prefer to work with the data as it comes (instead of adding helper columns) whenever possible.

Another idea that comes to mind is trying to use filters (either Table filters or sending the data into a PivotTable and use PT filters). Generally, when you apply filters on multiple columns they use AND logic … and we need OR logic.

So, let’s look at how to use the FILTER function as the base function to identify the orders that qualify for free shipping, and then summarize the results with the desired aggregate function.

Note: at the time of this writing, not all versions of Excel have the FILTER function. If your version of Excel doesn’t, please check out this post which talks about various alternatives.

Video

Details

We’ll perform the following steps:

• FILTER with OR logic
• Aggregate with COUNT, SUM, MAX
• Aggregate with ARRAYTOTEXT

Let’s do this thing.

FILTER with OR logic

The first thing we’ll do is identify the orders that qualify for free shipping with the FILTER function. This will be our base function that retrieves the orders, and then we’ll be able to aggregate the results of the FILTER function as desired.

The first step is to enter the criteria values for the three conditions into some cells, like this:

We’ll reference these values in our FILTER function, like this:

=FILTER(Table1[Order],(Table1[Date]=C9)+(Table1[Voucher]=C10)+(Table1[Amount]>C11))

Where:

• Table1[Order] is the column with the values to return
• (Table1[Date]=C9)+(Table1[Voucher]=C10)+(Table1[Amount]>C11) is the expression that defines which orders to include in the results. There are three conditions each enclosed in their own set of parens (). We use the addition operator + between each set of conditions to tell Excel to use OR logic. Note: If we had wanted AND logic, we would have used the multiplication operator * instead.

The results include the list of orders that qualify for free shipping:

Now, we’ll aggregate the results of the FILTER function with a variety of functions.

Aggregate with COUNT, SUM, MAX

For the free shipping orders, we would like to know the count, the total amount, and the largest order amount.

COUNT

Let’s count the number of orders that qualify for free shipping. We can simply wrap the COUNT function around our FILTER function, like this:

=COUNT(FILTER(Table1[Order],
(Table1[Date]=C9)+(Table1[Voucher]=C10)+(Table1[Amount]>C11)))

We hit Enter and we see the count of 7:

SUM

For the SUM, we don’t want to sum the order ids, we want to sum the order amounts. So, we need to update the FILTER function to return the Amount column rather than the Order column. So, we wrap the SUM function around our updated FILTER function like this:

=SUM(FILTER(Table1[Amount],
(Table1[Date]=C9)+(Table1[Voucher]=C10)+(Table1[Amount]>C11)))

We hit Enter and now have the total amount of free shipping orders:

MAX

To find the largest free shipping order amount, we’ll wrap the MAX function around the FILTER function, like this:

=MAX(FILTER(Table1[Amount],
(Table1[Date]=C9)+(Table1[Voucher]=C10)+(Table1[Amount]>C11)))

And we can continue to apply various aggregate functions as desired.

Now, let’s say we actually want to create a comma-separated list of the free shipping orders. For that, we’ll use ARRAYTOTEXT.

Aggregate with ARRAYTOTEXT

The ARRAYTOTEXT function converts a range of values into a single comma-separated list of values.

So, we can use ARRAYTOTEXT with FILTER, like this:

=ARRAYTOTEXT(FILTER(Table1[Order],
(Table1[Date]=C9)+(Table1[Voucher]=C10)+(Table1[Amount]>C11)))

We hit Enter, and bam:

Conclusion

And that is how we can use the FILTER function to identify transactions using OR logic, and then aggregate the results with helper functions.

If you found this post helpful or have any suggestions, please share by posting a comment below … thanks!

Sample file