How to Master Google Sheets Filter Function – Advanced Techniques

GiGiKing·March 16, 2024·0 comments

Are you ready to take your Google Sheets skills to the next level? In this article, I’m going to reveal some little-known and mind-blowing tips to use the AND and OR functions with the Filter function in Google Sheets. Trust me, you won’t find these techniques anywhere else!

But before we dive into the nitty-gritty, I recommend you check out some of our other tutorials that will make this one even more valuable to you:

Now, let’s explore the different scenarios in which you can use the AND, OR, and Filter functions together in Google Sheets.

Different Scenarios in AND, OR, FILTER Combination in Google Sheets

There are three key scenarios when combining AND, OR, and Filter functions in Google Sheets:

OR – Independently with Filter Formula.
AND – Independently with Filter Formula.
AND, OR – Combined with Filter Formula.

To help you understand these scenarios better, let’s use a sample dataset. Take a look at the image below:

Sample Data Set for OR, AND, FILTER combination

In this dataset, we have the marks of three students in three different subjects. Now, let’s define the conditions for passing the exam based on these scenarios:

Scenario 1: Students should score more than 49 marks in any one of the subjects to pass the exam.
Scenario 2: Students should score more than 49 marks in all three subjects to pass the exam.
Scenario 3: Students should score more than 49 marks in any two subjects to pass the exam.

Now, let’s explore how to use the Filter, AND, OR combination to solve these scenarios. Trust me, this will blow your mind!

How to Use AND, OR with Google Sheets Filter Function

OR with FILTER Function in Google Sheets (Any One Subject >=50, Passed)

Let’s start with the OR logical function combined with the Filter formula. Take a look at the image below:

OR, AND, Filter - sample

To achieve this using the IF logical formula with OR, you can use the following formula:

=IF(OR(B2>49,C2>49,D2>49),"PASSED","FAILED")

This formula checks if the student has scored greater than 49 marks in any subject. If true, the student is considered passed.

Now, let me share with you the magic formula using OR with the Filter function:

=IFERROR(FILTER(A2:D4,(B2:B4>=50)+(C2:C4>=50)+(D2:D4>=50)))

Notice that in the Filter function, you can replace the Boolean OR with the addition sign (+). This formula checks each student’s marks in all subjects and returns the passed students.

AND with FILTER Function in Google Sheets (All Subjects >=50, Passed)

Now, let’s move on to using the AND function with the Filter formula. Take a look at the conditional formula using IF:

=IF(AND(B2>49,C2>49,D2>49),"PASSED","FAILED")

To achieve the same result using the Filter function, you can use either of the following formulas:

=IFERROR(FILTER(A2:D4,(B2:B4>=50)*(C2:C4>=50)*(D2:D4>=50)))

=IFERROR(FILTER(A2:D4,B2:B4>=50,C2:C4>=50,D2:D4>=50))

Both formulas check if all the subjects’ marks are greater than 49. These formulas highlight the importance of using the AND function in the Filter function.

AND, OR with FILTER Function in Google Sheets (Any Two Subjects >=50, Passed)

Lastly, let’s explore the combination of AND, OR, and the Filter function. Take a look at the conditional formula using IF, OR, AND:

=IF(OR(AND(B2>49,C2>49),AND(B2>49,D2>49),AND(C2>49,D2>49)),"PASSED","FAILED")

Now, get ready for the grand finale! Here’s the advanced AND, OR, FILTER combination using the Filter function:

=IFERROR(FILTER(A2:D4,((B2:B4>=50)*(C2:C4>=50))+((B2:B4>=50)*(D2:D4>=50))+((C2:C4>=50)*(D2:D4>=50))))

Experiment with these formulas using your own data set to fully grasp their power. By understanding these advanced techniques, you’ll be able to handle complex filtering tasks like a pro!

If you want to see these formulas in action, you can access my sample sheet here.

I hope you’ve enjoyed this exclusive Google Sheets tutorial! If you found it helpful, please share it on Facebook, Twitter, and other social media platforms.

For more tips and tricks on Google Sheets, be sure to check out these additional resources:

Remember, mastering these advanced techniques will make you the ultimate Google Sheets ninja! Happy filtering!