Many Google Sheets users may not be familiar with the use of AND and OR functions in conjunction with IFS, as such scenarios are not commonly encountered.

The intentional inclusion of the word “Correctly” in the title serves a purpose. This is because, in many cases, AND and OR functions can be applied more efficiently using the IF function rather than IFS.

Let’s explore the correct usage of AND and OR functions with IFS in Google Sheets.

If you wish to examine individual examples of functions discussed here, please navigate to the “Functions Guide” in the menu above. You’ll find a comprehensive list of useful Google Sheets functions, including IF, IFS, AND, and OR.

This tutorial not only teaches the proper use of AND and OR within IFS but also provides insights into situations where IFS may not be the most suitable choice. The following examples will shed light on these considerations.

Before delving into the tutorial on how to correctly utilize AND and OR functions with IFS in Google Sheets, it’s important to clarify one more point. Based on my experience, I have observed that the IF function is better suited for handling AND and OR functions compared to IFS.

How to Correctly Use AND and OR Functions with IFS in Google Sheets – Examples

As mentioned earlier, in most cases, an IF function can outperform IFS, especially when used in conjunction with AND or OR. Therefore, rather than instructing on the correct usage of AND and OR functions with IFS, I aim to guide you on when to avoid such combinations.

Where Not to Use AND and OR with IFS

Example:

Here, you can understand why IF is preferable to IFS in combination with AND and OR functions. I am comparing the use of AND and OR functions with both IF and IFS.

The sample data illustrates the monthly sales volume of three products. In this example, I am specifically focusing on “Product 1,” as indicated in row 3 in all the formulas below.

Formula #1:

What result am I aiming for?

If the sales volume in any month in row #3 is >1000, I want the formula to return the text “Target Met.” This implies that if any of the values in cell B3, C3, or D3 is >1000, the formula should return “Target Met.” Either of the formulas below can be utilized.

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")

Take a look at how the above two formulas handle the OR function. I trust you can interpret the IF formula effectively. Now, let’s delve into IFS.

The IFS function assesses multiple conditions from left to right in the formula and returns a value corresponding to the first true condition. If no true condition is met, the formula will return a “#NA,” indicating a not available error. In our example, we cannot always ensure a true condition.

When an IFS formula encounters a false evaluation, it proceeds to the next one. In this case, the second test is 1*1=1, which is indeed true. This is the logic applied above. Alternatively, you can incorporate an IFERROR function with IFS, as shown below. The IFERROR function allows you to specify a custom text when IFS returns a “#NA” error.

=IFERROR(IFS(OR(B3>1000, C3>1000, D3>1000), "Target Met"), "Not Met")

In the above example, it’s preferable to stick with IF rather than IFS. The following “Formula 2” is comparable to the one above, but here you can understand how to use AND with IFS and IF. The appropriate choice is IF.

Formula #2:

The desired outcome in this case is:

If the sales volume in all three months is >1000, the formula should return the text “Target Met.” This implies that all values in cells 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 desired outcome is as follows:

If the sales volume in any of the two months is >1000, the formula should return the text “Target Met.”

OR and AND Functions 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 AND Functions 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 formulas above, you can observe that IF is more effective than IFS. In the meantime, I have aimed to provide a detailed explanation of the use of AND and OR functions with IFS in Google Sheets.

The Proper Use of AND and OR with IFS

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

As in the above examples, I am exclusively considering the sales volume of Product 1 in row #3.

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

Although both of the formulas below are acceptable, IFS is more efficient than IF in this scenario.

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 have gained an understanding of how to correctly use AND and OR functions with IFS in Google Sheets.

In reality, AND and OR functions don’t play as significant a role with IFS. IF is more effective in handling logical tests involving OR and AND. Therefore, it’s advisable to use these functions judiciously to maintain clarity in your sheets.

A well-structured formula is not only reader-friendly but also allows for easy modification if needed in the future.