The IF AND function in Google Sheets combines the functionality of the IF and AND functions to create an IF AND statement that is fulfilled if all the stated conditions are met. You can use numeric and string (text) conditions and even combine them together as IF AND conditions to help you identify the entries that you need.
The IF AND formula syntax is
=IF(AND(condition1, condition2, …), value_if_true, value_if_false)
Where
condition1 and condition2 are the conditions to fulfill. You can set more than two conditions
value_if_true is the value displayed if all the conditions are met
value_if_false is the value displayed if at least one of the conditions are not met
For this example, we identify the following conditions and values to display:
condition1: Weighted Score is greater than 36
condition2: Criterion A score is more than 14
Value_if_true: “Excellent”
Value_if_false: “Very Good”
As condition1 relies on data stored in column E and condition2 relies on data stored in column B, we get the following conditions (assuming row 2):
condition1: E2 > 36
condition2: B2 > 14
The function is
=if(and(E2>36,B2>14),"Excellent","Very Good")
You can extend IF AND down the column, giving you the following output:
For this example, we identify two conditions and values to display:
condition1: Assessment A is “Excellent”
condition2: Assessment B is “High Efficiency”
Value_if_true: “Shortlist”
Value_if_false: “Assess further”
As condition1 relies on data stored in column F and condition2 relies on data stored in column G, we get the following conditions (assuming row 2):
condition1: F2=“Excellent”
condition2: G2=“High Efficiency”
The strings should be enclosed in double quotes.
The function is
=if(and(F2="Excellent",G2="High Efficiency"),"Shortlist","Assess further")
You can extend IF AND down the column, giving you the following output:
For this example, we identify the following conditions and values to display:
condition1: Initial Recommendation is “Shortlist”
condition2: Criterion D is greater than 14
Value_if_true: “Admit”
Value_if_false: "Assess further”
As condition1 relies on data stored in column H and condition2 relies on data stored in column D, we get the following conditions (assuming row 2):
condition1: H2 = “Shortlist”
condition1: D2 > 14
The strings should be enclosed in double quotes.
In our example, the function is
=if(and(H2="Shortlist",D2>14),"Admit","Assess further")
You can extend IF AND down the column, giving you the following output:
There is theoretically no limit in the number of conditions you can add, but the limits come from the conditional logic you want to implement. This puts the realistic limit at to 2-3 conditions.
No. However, you can reference other cells when defining conditions.
IFTHEN: Allows you tor write statements that use IF X Then Y Logic
IF ELSE: Evaluates a condition, and takes one action when the result is TRUE and a different one when the result is FALSE.
Multiple IF: Learn how to use multiple if statements in a single formula
IF OR: Evaluates multiple conditions and produces a TRUE result when any of them are met.
IF Contains: Returns cells that contain a particular text.
How to Combine Text From Two Cells in Google Sheets
Count Colored Cells in Google Sheets (The Easy Way!)
