How to use multiple IF statements in Google Sheets
In Google Sheets, the IF statement allows you to specify a condition to test and one or more actions to take based on the result of that test. If the condition is true, the IF statement will execute one set of actions; if the condition is false, it will execute another set of actions. Here is the basic syntax for an IF statement in Google Sheets:
=IF(condition, value_if_true, value_if_false)
In today’s tutorial we’re going to learn how to implement multiple IF statements together to check various conditions at the same time. The IF statements used in such a way are called Nested IF statements. Let’s look at our dataset which has some sales records and we will create a ranking system for sales based upon their values in this dataset. We will create a check system which will rank sales based upon the following criteria;
- IF SALES <= 1000 THEN LOW SALES
- IF SALES <= 2000 THEN GOOD SALES
- IF SALES <= 3000 THEN BETTER SALES
- IF SALES > 3000 THEN GREAT SALES
The formula will be created by starting with the first IF condition and the next IF condition will be written in the value_if_false section of the first IF and so on. This way we can nest the IF conditions within one another.
Let’s implement this by following the steps below,
Method 1: Use various IF statements nested in one another
Step 1 – Implement multiple IFs using various IF statements
- We’ll use the following formula in an appropriate cell.
=IF(D2<=1000,“LOW SALES”,IF(D2<=2000,“GOOD SALES”,IF(D2<=3000,“BETTER SALES”,“GREAT SALES”)))
- After writing this formula in the first cell, double click the fill handle to implement it on all data range and we’ll get the desired results as shown above.
Method 2: Use IFS statement to implement multiple conditions
The IFS statement is an alternative to nested IFs and very easy to implement. The basic syntax of this is very easy and explained below;
=IFS(condition1, value1, [condition2, value2, …])
condition1 – The first condition to be evaluated. This can be a boolean, a number, an array, or a reference to any of those.
value1 – The returned value if condition1 is TRUE.
condition2, value2, … – Additional conditions and values if the first one is evaluated to be false.
Important Note:
If all conditions are FALSE, #N/A is returned.
Step 1 – Create the Nested IF Formula using IFS statements
- We’ll use the following formula in an appropriate cell.
=IFS(D2<=1000,“LOW SALES”,D2<=2000,“GOOD SALES”,D2<=3000,“BETTER SALES”,D2>3000,“GREAT SALES”)
- This will produce the same result as the previous formula but this one is way easier to implement and there are less chances of mistakes if we use this formula. After implementing this formula in the first cell, double click on the fill handle to implement it to the complete data range.