In this article:

May 8, 2024

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.

- Click on cell C2 and enter the formula: =IF(ISNUMBER(TIMEVALUE(B2)),IF(B2<E$2,"YES","NO"),"Invalid Data")

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”.

- Copy the formula from C2 down column C for each row of data.

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.

- Sort the data by column C to group the records for further processing

- Click on cell C2 and enter the formula:

**=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.

- Note that the final argument is returned only when none of the IF statements is true.

The formula checks each of multiple IF statements until a TRUE one is found.

- Be careful to apply correct Syntax because misplaced brackets or commas will cause errors.

The complexity of a multiple IF statement can easily result in undetected errors.

- Insert a final logical test TRUE in multiple IF Functions with a return value.
- If none of the previous conditions is true then this value is returned. e.g. =IFS(A1>150, "High", A1<100, "Low", TRUE, "OK") The value “OK” is returned if 100<=A1<=150

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.

- Click cell C2 and type: =IFS(B2<E$2,F$2,B2<E$3,F$3,B2<E$4,F$4,B2<E$5,F$5,B2<E$6,F$6,B2<E$7,F$7,B2>E$7,"NO MEDAL")

For simplicity valid data is assumed in this example.

The entire formula is enclosed between a single pair of brackets.

- The IFS function returns the same result and is far simpler.

- Click on cell D2 and type: =IFS(B2<G2,I2,TRUE,"")

- Copy the formula down column D for each row of data.

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.

- Prevent these errors by setting conditions that account for all scenarios.

Say for instance you have this function: =IFS(A1>100, "Yes", A1<100, "No")

- Add an additional condition: =IFS(A1>100, "Yes", A1<100, "No", A1=100, "Undecided")

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

Related IF Functions

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.

If you want to learn how to send bulk email from Google Sheets, we also suggest checking out our detailed guide.

Get Google Sheets productivity and automation tips delivered straight to your inbox

We'll email you 1-3 times a week — and never share your information.

Use Lido to connect your spreadsheets to email, Slack, calendars, and more to automate data transfers and eliminate manual copying and pasting. View all use cases ->