IFS

We can use an IFS function to accomplish what nested IFs do...

=IF(SUM(D:D) >= 1000000, "YES", IF(SUM(D:D) <= 10000, "THREAT LEVEL MIDNIGHT", "NO"))

...but in a cleaner fashion.

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 created with a nested IF...

=IF(SUM(D:D) >= 1000000, "YES", IF(SUM(D:D) <= 10000, "THREAT LEVEL MIDNIGHT", "NO"))

...with an IFS function?

Let's start with our first logical_test1!

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.

Check out our AND vs. OR article here!

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