Find out how to apply a number of filtering standards by combining AND and OR operations with the FILTER() operate in Excel

Making use of a number of standards in opposition to completely different columns to filter the info set in Microsoft Excel sounds troublesome but it surely actually isn’t as arduous because it sounds. An important half is to get the logic between these columns proper. For example, do you need to see all data the place one column equals x and one other equals y? Otherwise you may need to see all data the place one column = x or one other column equals y. The outcomes will likely be very completely different. On this article, I’ll present you embrace AND and OR operations in Excel’s FILTER() operate.

In a number of spots, you’ll learn “AND and OR,” which is grammatically awkward. I’m referring to the AND and OR operations generically. We received’t be utilizing the AND() and OR() capabilities. I’ll use uppercase solely to enhance readability.

SEE: 83 Excel suggestions each person ought to grasp (TechRepublic)

I’m utilizing Microsoft 365 on a Home windows 10 64-bit system. The FILTER() operate is out there in Microsoft 365, Excel On-line, Excel 2021, Excel for iPad and iPhone and Excel for Android tablets and telephones. I like to recommend that you simply wait to improve to Home windows 11 till all of the kinks are labored out. To your comfort, you possibly can obtain the demonstration .xlsx file.

The operations

The logic operators * and + specify a relationship between the operands in an expression (AND and OR, respectively). Particularly, * (AND) requires that each operands have to be true to return true. However, + (OR) requires that just one operand be true to return true. In our case, operand is an easy expression. For example

1 + 3 = 4 AND 2 + 1 = 3

returns true whereas

1 + 3 = 2 AND 2 + 1 = 3

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

1 + 3 = 4 OR 2 + 1 = 3

returns true as a result of not less than one expression is true and

1 + 3 = 2 AND 2 + 1 = 3

returns true for a similar motive.

Now let’s work via some examples.

Find out how to use the built-in filter in Excel

Let’s suppose that you simply monitor commissions utilizing the straightforward knowledge set proven in Determine A. Moreover, you need to know if anybody is falling under a selected benchmark—say, $200. Fortuitously, for customers who know use the built-in filters, you don’t even want the FILTER() operate. Nevertheless, utilizing the built-in characteristic works on the supply knowledge, and it’s troublesome to reference in different expressions, so whereas it’s simple, it may not be the best resolution for each state of affairs.

Determine A

We’ve two standards, personnel and fee. Let’s use a built-in characteristic to see the filtered set for James with any fee lower than or equal to $200, retaining in thoughts that you simply have to be working with a Desk object:

1. Click on the Personnel dropdown, uncheck (Choose All), verify James, and click on OK. Doing so will show 4 data.
2. From the Fee dropdown, select Quantity Filters after which select Much less Than Or Equal To, as proven in Determine B.
3. Enter 200 within the management to the best of the comparability operator (Determine C) and click on OK.

Determine B

Determine C

As you possibly can see in Determine D, James has just one fee that falls under the $200 benchmark: $160.

Determine D

It’s simple however does require a bit of data about how the characteristic works. However, the characteristic works with the info supply, and which may not be what you need.

Find out how to use * in Excel

You may need observed (Determine C) the AND and OR choices within the dialog the place you entered the benchmark quantity. This feature permits you to enter different standards for a similar column—Fee. To perform an AND throughout a number of columns, we’ll use the * image, which is analogous to the AND() operate, however AND() doesn’t work as you may count on when mixed with FILTER().

Determine E reveals the outcomes of coming into the next operate

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

into H5. At first, nothing occurs. That’s as a result of J2 and K2 are empty. Enter James into J2 and 200 into K2. Apply the suitable codecs to the ensuing filtered set under if mandatory. It’s the one buggy factor I don’t take care of—the dynamic array capabilities ignore formatting. Even after you apply it to the outcome set (columns H via Okay), it typically disappears when FILTER() recalculates.

Determine E

If you choose the completely different references, your operate will look a bit completely different attributable to structured referencing. Don’t fear about that; it’ll nonetheless work.

The primary argument, Table1, identifies the supply knowledge. The second argument

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

is what we’re considering. Merely put, the primary expression appears for a match to the worth in J2 within the Personnel column. With James as the factors for this column, this expression returns True. The second expression appears for match to the worth in K2 within the Fee column, and it too finds a worth that it lower than or equal to 200, so the expression returns True. Lastly, the operate returns the info from the rows that each return True. There’s just one, as earlier than.

To verify on different personnel, merely change the title in J2. To vary the fee benchmark, enter a brand new worth into K2. Take into account that the benchmark is at all times a lower than or equal to analysis as a result of <= is within the operate. Determine F reveals the results of checking on Rosa’s gross sales.

Determine F

The * character does an incredible job of permitting us to use standards throughout a number of rows. However what about OR?

Find out how to use + in Excel

Generally, you’ll need to verify for the existence of 1 worth or one other. When that’s the case, use the + image. We are able to see the distinction shortly sufficient by modifying the operate in H5: Exchange the * image with the + image. As you possibly can see in Determine G, there are three data that match the factors. The personnel worth is James, or the fee worth is lower than or equal to 200.

Determine G

And extra

In these easy examples, I labored with solely two columns, but it surely’s no drawback so as to add extra. If you do, the parentheses grow to be essential. For instance, let’s suppose you need to add one other particular person to the personnel standards, similar to Rosa—you need to match any document the place James or Rosa has a fee worth lower than 200. Once more, it’s a easy edit to the unique operate:

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

Determine H

Determine I

There are two data for James or Rosa the place the fee is lower than or equal to 200 (and). To incorporate much more personnel, enter extra rows and modify the operate by including a brand new expression, and keep in mind that whole OR must be wrapped in parentheses. The identical can be true when you had been referencing a number of expressions on the opposite facet. Wrap a number of AND expressions collectively and wrap a number of OR expressions collectively.