Still feeling nervous for your exam? Check out the Excel Cram Kit (Exam 2) for practice modules that emulate the difficulty that you'll face on your exam. Each module includes a thorough step-by-step explanation where I walk you through the entire problem solving process!
Nested IF & IFS explained
If you recall in the IF | Excel in 30 Minutes (Test #1) article, we created a spreadsheet for Michael Scott's C.R.I.M.E.A.I.D. (Crime Reduces Innocence Makes Everyone Angry I Declare) fundraising event.
With our spreadsheet, we created the following IF statement...
=IF(SUM(D:D) >= 1000000, "YES", "NO")
...to determine whether or not the goal of $1,000,000 was reached for the fundraiser. We then displayed this determination in cell F2.
Challenge question: What if the only potential outcome of the fundraiser was not just whether or not $1,000,000 was reached?
In other words... what if there were multiple conditions that could result in differing outcomes of the fundraiser?
This is where nested IF statements and IFS come into play!
Nested IF statements and IFS enable us to check if more than one condition is true, and returns the designated value for the first true condition.
To exemplify how we could incorporate multiple conditions into Michael's fundraiser, let's say that if the fundraiser does not surpass $10,000, then Michael Scott will be forced to sell his very own (and privately held) Threat Level Midnight movie to the local theatre.
Coding a nested IF
How can we incorporate this new condition into our current IF statement below?
=IF(SUM(D:D) >= 1000000, "YES", "NO")
With a nested IF statement!
We are currently just checking if the SUM is greater than or equal to $1,000,000. If that is true, then we want to display "YES" in the output. However, if that is false, we now need to check if it has not surpasses $10,000.
To do so, let's place the IF statement template into the spot where the IF statement currently has "NO".
=IF(SUM(D:D) >= 1000000, "YES",IF(logical_test, [value_if_true], [value_if_false]))
Notice how there is now an IF statement inside of an IF statement...
A nested IF statement is nested because there's an IF inside of an IF!
Within this nested IF statement, our logical_test is going to be assessing if the sum of all donations does not exceed $10,000. In other words, is the sum of all donations less than or equal to $10,000?
=IF(SUM(D:D) >= 1000000, "YES",IF(SUM(D:D) <= 10000, [value_if_true], [value_if_false]))
If that is the case (that all donations don't exceed $10,000), then we need to display "THREAT LEVEL MIDNIGHT" to the user within [value_if_true].
=IF(SUM(D:D) >= 1000000, "YES",IF(SUM(D:D) <= 10000, "THREAT LEVEL MIDNIGHT", [value_if_false]))
For [value_if_false], we need to consider what needs to happen if the fundraiser does not reach the $1,000,000 goal, but does exceed $10,000? In this case, we just need to display "NO".
=IF(SUM(D:D) >= 1000000, "YES",IF(SUM(D:D) <= 10000, "THREAT LEVEL MIDNIGHT", "NO"))
With our donations looking like so...
...when we plug this formula into F2, we get the following result:
Coding an IFS
We can use an IFS function to accomplish what we did before, but in a cleaner method.
IFS are essentially nested IF statements, but easier to read.
Here is the template for an IFS function:
=IFS(logical_test1, value1, [logical_test2, value2], [logical_test3, value3])
So, how can we write what we had above in this IFS function?
Let's start with our first logical_test1 and place our first condition in our nested IF above.
If the sum of all donations exceeds $1,000,000...
=IFS(SUM(D:D) >= 1000000, value1, [logical_test2, value2], [logical_test3, value3])
...then we want to display "YES".
=IFS(SUM(D:D) >= 1000000, "YES", [logical_test2, value2], [logical_test3, value3])
Now for our next logical_test2. In this case, let's figure out whether or not Michael will need to release Threat Level Midnight.
If the sum of all donations does not exceed $10,000...
=IFS(SUM(D:D) >= 1000000, "YES", SUM(D:D) >= 10000, value2, [logical_test3, value3])
...then we need to display "THREAT LEVEL MIDNIGHT".
=IFS(SUM(D:D) >= 1000000, "YES", SUM(D:D) >= 10000, "THREAT LEVEL MIDNIGHT", [logical_test3, value3])
Lastly, if none of these conditions are true... a.k.a. the sum of all donations lies somewhere between $10,000 and $1,000,000...
=IFS(SUM(D:D) >= 1000000, "YES", SUM(D:D) >= 10000, "THREAT LEVEL MIDNIGHT", AND(SUM(D:D) > 10000, SUM(D:D) < 1000000)), value3)
...then we need to display "NO" to the user.
=IFS(SUM(D:D) >= 1000000, "YES", SUM(D:D) >= 10000, "THREAT LEVEL MIDNIGHT", AND(SUM(D:D) > 10000, SUM(D:D) < 1000000), "NO")
When we plug this into F2, we get the same result as before!
It is important to note what's going on behind the scenes here...
First, we're checking if the sum exceeds $1,000,000, and if it does we're showing "YES".
=IFS(SUM(D:D) >= 1000000, "YES", SUM(D:D) >= 10000, "THREAT LEVEL MIDNIGHT", AND(SUM(D:D) > 10000, SUM(D:D) < 1000000)), "NO")
Then, we're checking if the sum is less than $10,000, and if it is we're displaying "THREAT LEVEL MIDNIGHT".
=IFS(SUM(D:D) >= 1000000, "YES", SUM(D:D) >= 10000, "THREAT LEVEL MIDNIGHT", AND(SUM(D:D) > 10000, SUM(D:D) < 1000000)), "NO")
Lastly, we're checking if the SUM if between $10,000 and $1,000,000, and if it is we're displaying "NO".
=IFS(SUM(D:D) >= 1000000, "YES", SUM(D:D) >= 10000, "THREAT LEVEL MIDNIGHT", AND(SUM(D:D) > 10000, SUM(D:D) < 1000000)), "NO")
IFS check for each condition in order, and returns the value of the first true condition it finds.
Practice problem
Imagine that you run a basketball team and want to create incentives based on their play. You have the following conditions going into your upcoming game (and their designated outputs), in order of importance:
- If John scores more than 12 points, everyone gets ice cream >>> "ICE CREAM"
- If Steve and Mary combine for at least 8 points, the team won't practice Friday >>> "NO PRACTICE"
- If Sarah scores above 25 points, she gets a medal >>> "MEDAL"
Considering the following stat line for the game, write an IFS function to determine what reward (if any) the team will receive.
=IFS(C2 > 12, "ICE CREAM", SUM(C5, C6) >= 8, "NO PRACTICE", C4 > 25, "MEDAL")
This IFS function returns "NO PRACTICE", because that was the first condition to return true!
Excel (Exam 2) Follow-along Guide
It's no secret you retain info better when you write it down. That's why I created the Excel (Exam 2) Follow-along Guide for you!
As you come upon key concepts highlighted in yellow, like this sentence here, you can fill-in-the-blanks on your Follow-along Guide so that you remember all the important stuff for later!
You can obtain the Excel (Exam 2) Follow-along Guide for FREE by entering your email below!
Next let's learn how to utilize all of the features of Excel tables!
Follow-along Guide
Fill-in-the-blanks for highlighted, key points in the concepts below!
Free Resources
Each exam concept broken down in simple, real-world examples!
 | Tools | Excel (Exam 2) Follow-along Guide |
 | Lesson | AND vs. OR |
| Lesson | HLOOKUP |
| Lesson | IFERROR |
| Lesson | Nested IF vs. IFS |
| Lesson | Excel tables |
| Lesson | PivotTables |
| Lesson | COUNTIF, SUMIF, AVERAGEIF |
| Lesson | SUMPRODUCT |
| Lesson | 3D Cell References |
Practice Problems
Step-by-step walkthrough for each of the questions you need to be ready for!
"I got this review for my 2nd CSE exam after not doing so well on the first one, and it was so helpful! Will definitely be using again in the future."
Erin Graham
Oct. 26, 2021
I’m a Miami University (OH) 2021 alumni who majored in Information Systems. At Miami, I tutored students in Python, SQL, JavaScript, and HTML for 2+ years. I’m a huge fantasy football fan, Marvel nerd, and love hanging out with my friends here in Chicago where I currently reside.