If you want to run a logical test on a Google Sheets formula, giving different results, whether the test is TRUE or FALSE, you will need to use the IF function. Here’s how to use it in Google Spreadsheets.
As the name suggests, IF is used to test whether a single cell or range of cells meets certain criteria in a logic test, where the result is always TRUE or FALSE.
If the IF test is TRUE, Google Spreadsheets will return a number or string of text, perform a calculation, or run another formula.
If the result is FALSE, it will do something completely different. You can combine IF with other logical functions such as AND and OR or with other nested IF statements.
Use the IF function
The IF function can be used alone in a single logical test, or you can combine multiple IF statements into a single formula for more complex tests.
To get started, open it sheet of Google Spreadsheets Calculation and then write = IF (test, value_if_true, value_if_false) in a cell.
Replace «test» with your logical test, and then replace the «value_if_true» and «value_if_false» arguments with the operation or result that Google Spreadsheets will provide when the result is TRUE or FALSE.
In the example below, an IF statement is used to test the value in cell B3. If cell B3 contains the letter B, the value TRUE will be returned to cell A3. In this case, it is a string of text containing the letter A.
If cell B3 does not contain the letter B, cell A3 will return the value FALSE, which in this example is a text string containing the letter C.
In the example shown, cell B3 contains the letter B. The result is TRUE, so the TRUE result (letter A) is returned to A3.
The calculations also work as proof of logic. In the following example, the formula IF in cell A4 tests whether cell B4 has a numeric value equal to or greater than the number 10. If the result is TRUE, it returns the number 1. If it is false, it returns the number 2.
In the example, cell B4 has a value of 9. This means that the logic test result is FALSE, with the number 2 displayed.
Nested IF statements
For a longer and more complex logic test, you can merge several IF statements into the same formula.
To merge several IF statements together into a single formula, type = IF (first_test, value_if_true, IF (second_test, value_if_true, value_if_false)). While this shows only one nested IF statement, you can nest together how many IF statements you need.
For example, if cell B3 is equal to 4, then the IF formula in A3 returns 3. If cell B3 is not equal to 4, then a second IF statement is used to test whether cell B3 has a value less than 10.
If yes, it returns the number 10. Otherwise, it returns a 0. This test example has its own IF statement nested as the first «value_if_false» argument, which requires the first test to be FALSE before the test is considered.
The example above shows the three potential results of this test. With the first logic test (B3 equals 3) returning a TRUE result, the IF formula in cell A3 returned the number 4.
The second logic test returned another TRUE result in cell A4, with the value of B4 less than 10.
The only FALSE result is returned in cell A5, where the result of both tests (if B5 is equal to or less than 10) is FALSE, returning the FALSE result (a 0).
You can use a nested IF statement as a «value_if_true» argument in the same way. To do this, type = IF (first_test, IF (second_test, value_if_true, value_if_false), value_if_false).
For example, if cell B3 contains the number 3 and if cell C3 contains the number 4, it returns a 5. If B3 contains a 3, but C3 does not contain a 4, it returns a 0.
If B3 does not contain a 3, it returns the number 1.
The results of this example show that for the first test to be true, cell B3 must be equal to number 3.
From there, ‘value_if_true’ for the initial IF uses a second nested IF statement to do a second test (if C3, C4, C5 or C6 contains the number 4). This gives you two possible «value_if_false» results (a 0 or a 1). This is the case for A4 and A5 cells.
If you do not include a FALSE argument for the first test, Google Spreadsheets will return an automatic FALS text value for you. This is shown in cell A6 of the previous example.
Using IF with AND and OR
Because the IF function performs logical tests, with TRUE or FALSE results, it is possible to nest other logical functions such as AND and OR in an IF formula. This allows you to run an initial test with multiple criteria.
The AND function requires that all test criteria be correct in order to return a TRUE result. Or it requires only one of the test criteria to be correct for a TRUE result.
To use IF AND, type = IF (AND (AND Argument 1, AND Argument 2), value_if_true, value_if_false). Replace the AND arguments with your own and add as many as you want.
To use IF OR, = IF (OR (OR Argument 1, OR Argument 2), value_if_true, value_if_false). Replace and add as many arguments OR you need.
This example shows IF AND and IF OR which are used to test the same values in columns B and C.
For IF Y, B3 must be equal to 1 and C3 must be less than 5 for A3 to return a string of text «Yes». Both results are TRUE for A3, with one or both results FALSE for A4 and A5 cells.
For IF O, only one of these tests (B3 is equal to 1 or C3 is less than 5) must be TRUE. In this case, both A8 and A9 return TRUE, because one or both of the results in columns B and C are correct. Only A10, with two failed results, returns FALSE.