How to Correctly Use AND, OR Functions with IFS in Google Sheets

0
103
How to Correctly Use AND, OR Functions with IFS in Google Sheets

Many Google Sheets users are not well accustomed with the use of AND, OR functions with IFS. Because such uses aren’t commonly occurring. Here in the title, I have intentionally used the word “Correctly”. You know why? Because in most cases, you can use AND, OR functions more efficiently with IF function than IFS. Let’s see how to correctly use AND, OR functions with IFS in Google Sheets.

If you want to go through individual examples of any of the functions used here, please go to the menu Functions on the navigation bar above. You can find all the useful Google Sheets functions there including, IF, IFS, AND and OR.

With this tutorial, you can not only learn the use of AND, OR in IFS but also you can learn where you should not or avoid using IFS. The following examples can shed some light in to this.

Before going to the tutorial on how to correctly use AND, OR functions with IFS in Google Sheets, I want to clear one more thing. From my experience, I found that IF is better handling AND, OR functions not IFS.

How to Correctly Use AND, OR Functions with IFS in Google Sheets – Example

As I’ve told you above, in most cases an IF can do better than IFS especially while using AND, OR together with it. So instead of telling you how to correctly use AND, OR functions with IFS, I’m trying to tell you where to avoid such combination.

Where Not to Use AND, OR with IFS

Example:

Where Not to Use AND, OR with IFS

Here you can see, why IF is better than IFS in combination with AND, OR Functions. I am here comparing a combination of AND, OR Functions with IF and IFS.

The sample data shows the monthly sales volume of three products. Here in the example, I’m only considering “Product 1” in row 3 in our all formulas below.

Formula 1:

What result I want?

If sales volume in any of the month in row # 3 is >1000, I want the formula to return the text “Target Met”. That means any of the values in cell B3, C3 or D3 is >1000, the formula should return “Target Met”. We can use either of the formulas below.

OR Function in Combination with IF in Google Sheets

=IF(OR(B3>1000,C3>1000,D3>1000),”Target Met”,”Not Met”)

OR Function in Combination with IFS in Google Sheets

=IFS(OR(B3>1000,C3>1000,D3>1000),”Target Met”,1*1=1,”Not Met”)

Just see how the above two formulas handling OR function. I hope you can read the IF formula well. Now about IFS.

The IFS function evaluates multiple conditions from left to right in the formula and returns a value that corresponds to the first true condition. If there is no true condition, then the formula would return “#NA”, not available error. In our example we can’t always guarantee a true.

When an IFS formula finds the first evaluation is false, it moves to the second. Here the second test is 1*1=1 and which is definitely true. That is the logic I’ve applied above. Alternatively you can use an IFERROR function as below with IFS. IFERROR can return a custom text when the IFS returns “#NA” error.

=iferror(IFS(OR(B3>1000,C3>1000,D3>1000),”Target Met”),”Not Met”)

So in the above example better to stick with IF not IFS. The following “Formula 2” is similar to the above. But here you can learn how to use AND with IFS and IF. Needless to say the correct use is IF.

Formula 2:

The result that I want here is;

If sales volume in all the three months are >1000, then I want the formula to return the text “Target Met”. That means all the values in the cell B3, C3 and D3 should be >1000.

AND Function in Combination with IF in Google Sheets

=IF(AND(B3>1000,C3>1000,D3>1000),”Target Met”,”Not Met”)

AND Function in Combination with IFS in Google Sheets

=IFS(AND(B3>1000,C3>1000,D3>1000),”Target Met”,1*1=1,”Not Met”)

Formula 3:

The result I want:

If sales volume in any of the two months are >1000, I want the formula to return the text “Target Met”.

OR, AND Function in Combination with IF in Google Sheets

=IF(OR(AND(B3>3000,C3>3000),AND(B3>3000,D3>3000),AND(C3>3000,D3>3000)),”Target Met”,”Not Met”)

OR, AND Function in Combination with IFS in Google Sheets

=IFS(OR(AND(B3>3000,C3>3000),AND(B3>3000,D3>3000),AND(C3>3000,D3>3000)),”Target Met”,1*1=1,”Not Met”)

In all the above formulas, you can see that IF is better than IFS. Between the lines, I’ve detailed the use of AND, OR Functions with IFS in Google Sheets.

The Proper Use of AND, OR with IFS

Here I am explaining you the proper use of AND with IFS. In similar situations you can use OR with IFS.

Just like the above examples, here also I am only considering the sales volume of Product 1 in row # 3.

Where to Use AND, OR with IFS

Here if the sum of B3:D3 is <2500, the result should be “Poor”, if it’s >=2500 and <6000, the result should be “Average” and if it’s >=6000, the result should be “High”

Though both of the below formulas are fine to use, here an IFS is better than IF.

IF with AND

=if(sum(B3:D3)<2500,”Poor”,if(and(sum(B3:D3)>=2500,sum(B3:D3)<6000),”Average”,if(sum(B3:D3)>=6000,”High”)))

IFS with AND

=ifs(sum(B3:D3)<2500,”Poor”,and(sum(B3:D3)>=2500,sum(B3:D3)<6000),”Average”,sum(B3:D3)>=6000,”High”)

Conclusion:

With this tutorial, I hope you could understand how to correctly use AND, OR functions with IFS in Google Sheets. Actually AND, OR has not much role with IFS. IF is better in handling OR, AND in logical test. So use the functions intelligently to avoid clutter in your sheets. A well formed formula is reader friendly and in a future date, if you want you can easily modify a well formed formula.

LEAVE A REPLY

Please enter your comment!
Please enter your name here