How to Use AND,OR with Google Sheets Filter Function – ADVANCED Use

0
111
AND,OR with Google Sheets Filter Function

I am going to share you a very rare and tricky use of AND,OR with Google Sheets Filter Function. I’m sure it’s totally new to you. What else! I am going to compare AND,OR,FILTER Formula with AND,OR,IF Logical Formula.

We have already detailed tutorials on Google Sheets FILTER, AND, OR, IF Functions. Now here I will explain you how to use AND,OR with Google Sheets Filter Function. So, before going to follow this very rare Google Sheets tutorial, I advise you to please go through our below spreadsheet tutorials which can make this tutorial worthy.

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

There are three different scenarios in the use of AND, OR, Filter Formula combination. What are they?

  1. OR – Independently with FILTER Formula.
  2. AND – Independently with FILTER Formula.
  3. AND, OR – Combined with FILTER Formula.

Let me explain you what type of data we are going to filter using this FILTER, AND, OR combination.

Sample Data Set for OR,AND,FILTER combination

The above data shows the marks of three students out of 100 in three different subjects. We are setting up three different conditions to find whether they are passed or failed. I mean the three scenarios in the use of Filter, AND, OR combination.

1. Condition or Scenario 1: Students should score more than 49 marks in any one of the subjects to pass the exam.

2. Condition or Scenario 2: Students should score more than 49 marks in all three subjects to pass the exam.

3. Condition or Scenario 3: Students should score more than 49 marks in any two subjects to pass the exam.

Here I am going to use FILTER, AND, OR combination to check the above three different scenarios. Also I’am doing the same with IF, AND, OR combination.

How to Use AND,OR with Google Sheets Filter Function

Scenario 1: Any One Subject >=50, Passed

OR with FILTER in Google Sheets:

Here we should use the OR logical function with Filter Formula. Once again, see the data below.

OR, AND, Filter - sample

First let us see how to deal with this using IF Logical Formula with OR.

=IF(OR(B2>49,C2>49,D2>49),”Passed”,”Failed”)

For the first student, we can use this formula. Then we can copy and paste this formula to down. See how I used OR with IF formula here. Here, if the student scored “>49” or you can say “>=50” in any subject, that student is considered as passed. Now you can see how can we use OR with Filter function. Here is that awesome formula.

Note:

In FILTER function, you can replace the Boolean OR by Addition “+”

Also In FILTER function, you can replace the Boolean AND by multiplication “*”

Here is now the use of OR with FILTER in Google Spreadsheets.

=iferror(filter(A2:D4,(B2:B4>=50)+(C2:C4>=50)+(D2:D4>=50)))

Explanation with reference to Syntax:

FILTER(range, condition1, [condition2])

Here “A2:D4” is the range.

Now “(B2:B4>=50)+(C2:C4>=50)+(D2:D4>=50)” is the condition # One. Since there is no comma between OR, Google Sheets considers it as a single condition.

Scenario 2: All Subjects >=50, Passed

AND with FILTER in Google Sheets:

Here we require to use AND with Filter Formula. Same as above, here also first let us see how this can achieve with IF Logical Formula.

=IF(AND(B2>49,C2>49,D2>49),”Passed”,”Failed”)

Now see how to use FILTER with AND Logical Function. As I’ve told you above, you should use multiplication “*” to replace the Boolean AND. So the formula will be as below.

=iferror(filter(A2:D4,(B2:B4>=50)*(C2:C4>=50)*(D2:D4>=50)))

But it’s not required! A normal FILTER function can be used as below to get the same filter.

=iferror(filter(A2:D4,B2:B4>=50,C2:C4>=50,D2:D4>=50))

Both the above check whether all the marks are greater than >49.

Here lies the secret! Many advanced Google Sheets users ignore the importance of AND in FILTER as they think it’s not necessary. The below example can tell you, how important is the AND, FILTER combination in Google Sheets.

Scenario 3: Any Two Subjects >=50, Passed

AND, OR with FILTER in Google Sheets:

As earlier, first see the combination of IF, OR, AND formula to solve the issue.

=IF(OR(AND(B2>49,C2>49),AND(B2>49,D2>49),AND(C2>49,D2>49)),”Passed”,”Failed”)

Now here is the advanced AND, OR, FILTER combination in Google Sheets.

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

Carefully check all the formulas and experiment with your own data set to learn this trick. If you want you can copy and use my sample sheet. Below is the sample spreadsheet where I’ve used all these AND,OR with Google Sheets Filter Function variations.

Sample Sheet with FILTER,AND,OR and IF,AND,OR combination.

Please share or like if you have enjoyed this Google Sheets tutorial.

LEAVE A REPLY

Please enter your comment!
Please enter your name here