Table of Contents
The IF and AND functions in Google Sheets can be combined in order to perform more complex logical tests. The IF function returns a value based on a logical test, while the AND function allows multiple logical tests to be performed. By combining these two functions, you can create a more complex test that can return multiple values based on a combination of criteria. This is useful for calculations that require multiple criteria to be met in order for the result to be accurate.
You can use the following basic syntax to use the IF and AND functions together in Google Sheets to determine if some cell meets several criteria:
=IF(AND(A1="String", B1>10), "value1", "value2")
If the value in cell A1 is equal to “String” and if the value in cell B1 is greater than 10, then we return value1, otherwise we return value2.
Note that we can use as many logical comparisons as we’d like within the AND function.
The following examples show how to use this syntax in practice.
Related:
Example 1: Combine IF and AND Functions with String Comparisons
Suppose we have two columns in Google Sheets that contain the conference and number of wins for various NBA teams:
Suppose we classify a team as “Good” if they have more than 40 wins.
We can use the following formula with the IF and AND functions to determine if each team is in the West and Good:
=IF(AND(A2="West", B2>40), "Yes", "No")
The following screenshot shows how to use this syntax in practice:
If a given team is in the West and they have more than 40 wins, we return a value of “Yes”, otherwise we return “No.”
Example 2: Combine IF and AND Functions with Numeric Comparisons
Suppose we have columns that contain the number of points and assists for various basketball players and we’d like to classify each player as “Good” or “Bad.”
Let’s say that if a player has more than 20 points and more than 5 assists, we will classify them as “Good”, otherwise we’ll classify them as “Bad.”
We can use the following formula with the IF and AND functions to determine if each player should be classified as “Good” or Bad”:
=IF(AND(A2>20, B2>5), "Good", "Bad")
The following screenshot shows how to use this syntax in practice:
If a given player has more than 20 points and more than 5 assists, we classify them as “Good.”
Otherwise we classify them as “Bad.”
The following tutorials explain how to perform other common operations in Google Sheets: