Applying multiple criteria against different columns to filter the data set in Microsoft Excel sounds difficult but it really isn’t as hard as it sounds. The most important part is to get the logic between those columns right. For instance, do you want to see all records where one column equals x and another equals y? Or you might want to see all records where one column = x or another column equals y. The results will be very different. In this article, I’ll show you how to include AND and OR operations in Excel’s FILTER() function.

In several spots, you’ll read “AND and OR,” which is grammatically awkward. I’m referring to the AND and OR operations generically. We won’t be using the AND() and OR() functions. I’ll use uppercase only to improve readability.

SEE: 83 Excel tips every user should master (TechRepublic)

I’m using Microsoft 365 on a Windows 10 64-bit system. The FILTER() function is available in Microsoft 365, Excel Online, Excel 2021, Excel for iPad and iPhone and Excel for Android tablets and phones. I recommend that you wait to upgrade to Windows 11 until all the kinks are worked out. For your convenience, you can download the demonstration .xlsx file.

The operations

The logic operators * and + specify a relationship between the operands in an expression (AND and OR, respectively). Specifically, * (AND) requires that both operands must be true to return true. On the other hand, + (OR) requires that only one operand be true to return true. In our case, operand is a simple expression. For instance

1 + 3 = 4 AND 2 + 1 = 3

returns true while

1 + 3 = 2 AND 2 + 1 = 3

returns false. If we apply the OR operation to the same expressions

1 + 3 = 4 OR 2 + 1 = 3

returns true because at least one expression is true and

1 + 3 = 2 AND 2 + 1 = 3

returns true for the same reason.

Now let’s work through some examples.

How to use the built-in filter in Excel

Let’s suppose that you track commissions using the simple data set shown in Figure A. Furthermore, you want to know if anyone is falling below a specific benchmark—say, $200. Fortunately, for users who know how to use the built-in filters, you don’t even need the FILTER() function. However, using the built-in feature works on the source data, and it’s difficult to reference in other expressions, so while it’s easy, it might not be the right solution for every situation.

Figure A

We have two criteria, personnel and commission. Let’s use a built-in feature to see the filtered set for James with any commission less than or equal to $200, keeping in mind that you must be working with a Table object:

1. Click the Personnel dropdown, uncheck (Select All), check James, and click OK. Doing so will display four records.
2. From the Commission dropdown, choose Number Filters and then choose Less Than Or Equal To, as shown in Figure B.
3. Enter 200 in the control to the right of the comparison operator (Figure C) and click OK.

Figure B

Figure C

As you can see in Figure D, James has only one commission that falls below the $200 benchmark: $160.

Figure D

It’s easy but does require a bit of knowledge about how the feature works. On the other hand, the feature works with the data source, and that might not be what you want.

How to use * in Excel

You might have noticed (Figure C) the AND and OR options in the dialog where you entered the benchmark amount. This option allows you to enter other criteria for the same column—Commission. To accomplish an AND across multiple columns, we’ll use the * symbol, which is similar to the AND() function, but AND() doesn’t work as you might expect when combined with FILTER().

Figure E shows the results of entering the following function

=FILTER(Table1, (Table1[Personnel]=J2) * (Table1[Commission]<=K2), “No Results”)

into H5. At first, nothing happens. That’s because J2 and K2 are empty. Enter James into J2 and 200 into K2. Apply the appropriate formats to the resulting filtered set below if necessary. It’s the one buggy thing I don’t care for—the dynamic array functions ignore formatting. Even after you apply it to the result set (columns H through K), it often disappears when FILTER() recalculates.

Figure E

If you select the different references, your function will look a bit different due to structured referencing. Don’t worry about that; it will still work.

The first argument, Table1, identifies the source data. The second argument

(Table1[Personnel]=J2) * (Table1[Commission]<=K2)

is what we’re interested in. Simply put, the first expression looks for a match to the value in J2 in the Personnel column. With James as the criteria for this column, this expression returns True. The second expression looks for match to the value in K2 in the Commission column, and it too finds a value that it less than or equal to 200, so the expression returns True. Finally, the function returns the data from the rows that both return True. There’s only one, as before.

To check on other personnel, simply change the name in J2. To change the commission benchmark, enter a new value into K2. Keep in mind that the benchmark is always a less than or equal to evaluation because <= is in the function. Figure F shows the result of checking on Rosa’s sales.

Figure F

The * character does a great job of allowing us to apply criteria across multiple rows. But what about OR?

How to use + in Excel

Sometimes, you’ll want to check for the existence of one value or another. When that’s the case, use the + symbol. We can see the difference quickly enough by modifying the function in H5: Replace the * symbol with the + symbol. As you can see in Figure G, there are three records that match the criteria. The personnel value is James, or the commission value is less than or equal to 200.

Figure G

And more

In these simple examples, I worked with only two columns, but it’s no problem to add more. When you do, the parentheses become very important. For example, let’s suppose you want to add another person to the personnel criteria, such as Rosa—you want to match any record where James or Rosa has a commission value less than 200. Again, it’s a simple edit to the original function:

1. Select H5.
2. Enter (Table1[Personnel]=J3) * between the first and second expressions for the second argument (Figure H). Wrap the first two expression in parentheses: ((Table1[Personnel]=J2) + (Table1[Personnel]=J3)) * (Table1[Commission]<=K2). That extra set of parentheses calculates the OR operation first.
3. Enter Rosa in J3 to see the results in Figure I.

Figure H

Figure I

There are two records for James or Rosa where the commission is less than or equal to 200 (and). To include even more personnel, enter more rows and modify the function by adding a new expression, and remember that entire OR needs to be wrapped in parentheses. The same would be true if you were referencing multiple expressions on the other side. Wrap multiple AND expressions together and wrap multiple OR expressions together.