hen you use multiple IF statements (functions) inside the same Google Sheets function they are called nested IF statements because each one is inside the next. Nesting enables multiple conditional checks in a single Google Sheets formula.
IF(expression1, value_if_true1, IF(expression2, value_if_true2, IF(expression3, value_if_true3, value_if_false)))
You can see all of the examples presented below in our sample Google Sheets spreadsheet here.
The first IF function tests the validity of Elapsed_Time data and returns “Invalid Data” if a value is out of range or the wrong format.
The second IF function checks whether the competitor deserves a medal and returns either “YES” or “NO”.
A simple combination of 2 IF statements cannot award the appropriate medal for each competitor.
However it can identify medal winners and screen the data for validity.
=IF(B2<E$2, "Gold", IF(B2<E$3, "Silver", IF(B2<E$4, "Bronze/Silver", IF(B2<E$5, "Titanium", IF(B2<E$6, "Bronze", IF(B2<E$7, "Copper", IF(B2>=E$7, "NO MEDAL")))))))
For simplicity, valid data is assumed in this example.
Correct allocation from 6 different medals requires an IF statement for each possibility.
The formula checks each of multiple IF statements until a TRUE one is found.
The complexity of a multiple IF statement can easily result in undetected errors.
It can be better to use the Google Sheets IFS function when you want to combine multiple IF statements. The syntax is simpler to apply which makes finding errors much easier.
You can read our full IFS Function tutorial by clicking here:
=IFS(condition1, value1, [condition2, value2, …])
You can see all of the examples presented below in our sample spreadsheet here.
For simplicity valid data is assumed in this example.
The entire formula is enclosed between a single pair of brackets.
The IFS function lists the quantities of only ingredients to be purchased for baking bread.
The IFS function in Google Sheets yields an "#N/A" error if none of the conditions is true.
Say for instance you have this function: =IFS(A1>100, "Yes", A1<100, "No")
Google Sheets does not limit the number of Nested IF functions, but in practice they become unmanageable long before the 4000 character limit per cell is reached.
Use multiple IF statements to sort data into several categories in Google Sheets in a single step.
Multiple IF statements appear deceptively simple to apply and are widely used in Google Sheets, but the risk of logic errors is proportional to the number of conditions being tested.
Be very careful to use the correct Syntax when Nesting IF statements in Google Sheets, because serious errors can occur and go undetected in large datasets
IF: Allows you to check for specific conditions across a dataset
SUMIF: Use to sum numbers if they meet a certain condition..
SUMIFS: Sums data from cells that meet multiple criteria
COUNTIF: count data if it fulfils certain criteria
COUNTIFS: Count data that fulfils two or more criteria.
COUNT IF Not Null: Count cells if they contain data
IFERROR: Allows you to output values when an Error in a formula occurs
IF THEN: Allows you tor write statements that use IF X Then Y Logic
IF AND: Combines the functionality of the IF and AND functions
IF Else: Set conditions that give an output depending on whether a given condition is fulfilled or not
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.
AVERAGEIF: Calculate an average for numbers within a data range if they meet the provided criteria.
Use our software on invoice approval to easily set up custom reminders from your spreadsheet in just a few clicks.
If you want to learn how to send bulk email from Google Sheets, we also suggest checking out our detailed guide.
Save hours on repetitive and tedious work. Lido is a new spreadsheet that connects your spreadsheets, forms, PDFs, and email inbox.
.png)
