Conditional Functions in Spreadsheets

When someone is taught pretty much any programming language, one of the first things that arise are conditionals. That is the classical IF-ELSEIF-ELSE statements, which allow programs to perform different operations based on logical conditions. However, this may not be the first thing that one thinks of in Excel. Nevertheless, they are there, and they are quite important when you are working on Excel reports with a certain amount of complexity. In particular, this tutorial we will cover how IF-ELSE statements are structured in Excel, as well as, some more advanced functions like COUNTIF() that come in handy in certain situations.

Basic IF Statements

To begin, let's start with an overview of how to write a basic IF statement in Excel. The syntax is as follows:

IF(condition, value_if_true, value_if_false)

Where:

• condition: value or a logical operation that evaluates to TRUE or FALSE
• value_if_true: The value to return if the condition is TRUE
• value_if_false: The value to return if the condition is FALSE

With that said, let's jump into a simple example of how to use a basic conditional using a toy table:

Simple right? For those of you familiar with R, you probably have noticed that this syntax is essentially the same as the ifelse() function. However, there are a few things that are a bit different. For instance, logical operators. In this simple example, I have used the ">" logical operator, which is a standard way of indicating greater than. Nevertheless, others aren't so standard. This includes the not equal to operator, which is written as "<>"

A complete account of all the logical operators used in Excel can be seen below:

OR(), AND() and NOT()

Comparison operators are not the only components of Excel conditional statements that may differ from those that you may use in Python, R, or Matlab. In fact, the way that you define boolean operations like OR, AND, and NOT is different. For example, in Excel, you would write an OR expression like this:

IF(OR(value_1 = k, value_2 = y), value_if_true, value_if_false)

To see all boolean operators in action inside Excel, look at the example below:

Nested IF Statements

Just as you can nest IF-ELSEIF-ELSE statements in Python, R, or Matlab, you can also do it in Excel. To do so, it is as simple as adding another IF statement inside a previous IF statement when this one evaluates to TRUE or FALSE. This allows you to test more conditions and return more results based on these conditions. Excel can nest up to 64 IF statements. However, I strongly advise against nesting too many IF statements as you need to be careful with the logic, and debugging it or changing it down the line can become very cumbersome.

With that said, here is a simple use case of nested IFs inside Excel itself:

COUNTIF() and COUNTIFS()

Aside from traditional IF statements, Excel has other functions that can perform operations based on a set of given conditions. In this section, I am going to focus on conditional counting functions, and, more specifically, in the functions COUNTIF() and COUNTIFS().

COUNTIF() allows the user to count cells that meet a single criterion. Its syntax is as follows:

COUNTIF(cell_range_to_count, criteria)

Where:

• cell_range_to_count: The specific range of cells that you wish to count
• criteria: The criteria that controls which cells should be counted. These can be very diverse, for example:
  • COUNTIF(A1:A10, 10) - This would count how many cells in the range from cell A1 to cell A10 are equal to 10
  • COUNTIF(A1:A10, B5) - This would count how many cells in the range from cell A1 to cell A10 are equal to the value in cell B5
  • COUNTIF(A1:A10, "Gandalf") - This would count how many cells in the range from cell A1 to cell A10 are equal to "Gandalf"
  • COUNTIF(A1:A10, ">20") - This would count how many cells in the range from cell A1 to cell A10 are greater than 20
  • COUNTIF(A1:A10, "<>Gandalf") - This would count how many cells in the range from cell A1 to cell A10 are not equal to "Gandalf"

COUNTIFS, as you might have guessed, differs from COUNTIF in that it allows the user to count cells that meet multiple criteria. Its syntax is as follows:

COUNTIFS(cell_range_to_count_1, criteria_1, cell_range_to_count_2, criteria_2,...cell_range_to_count_n, criteria_n)

It is important to note that for each additional range that you specify in COUNTIFS, you must have the same number of rows and columns as the original range (cell_range_to_count_1) otherwise you will get an error.

These two functions are fantastic to use with business reports. One of the best examples for me being how easy they make counting the number of appointments our sales representatives make every month and where are they located in our sales funnel.

In the videos below, I'll illustrate how these two functions work in action. First, let's start with just counting the total appointments of a set of fictional salespeople:

Wasn't that easy? You just tallied how many appointments each of our fictitious salespeople had in total. In theory, you could have done this as well using just basic IF statements, but it would have required quite a bit more labor for the same exact result.

Now, let's say you also want to tally the appointments that each of these salespeople had each month. In this case, COUNTIFS() is the answer; you can see it in action in the video below:

Note: You may have noticed that I covered the range that I wanted to count in the last video in $ signs. This is done in order to lock it down and prevent it from changing when I drag the formula down.

Voilá, we tallied the appointments of our dummy salespeople by month without breaking a sweat. It is worth mentioning that you could do this in a more "efficient" way if you play with the $ signs to lock down certain cells and ranges from moving as you drag the formulas around (i.e., I could have just written the formula in F2 and then dragged it around to the rest). However, I wanted to be more explicit writing the COUNTIFS formulas for January and February to provide more visibility. If you are curious though, I encourage you to try to rewrite the COUNTIFS() formula in F2, so that you can drag it around the remaining cells while getting the same results. This may be a useful exercise as it is very convenient to play with locking down ranges and cells to drag formulas when your Excel business reports get large. Here is the data table used in this tutorial.

SUMIF() and SUMIFS()

We have seen how you can count cells that meet a single criterion or multiple criteria with COUNTIF() and COUNTIFS(), but what if you need to add the cells instead of just counting them? Enter SUMIF() and SUMIFS(). These functions are particularly useful when you have to add the revenue that a given sales person has generated in total or in a given month, for example. The syntax of both functions is as follows:

SUMIF(criteria_range, criteria, (optional) sum_range)

SUMIFS(sum_range, criteria_range_1, criteria_1, criterion_range_2, criteria_2...criteria_range_n, criteria_n)

Where:

• criteria_range: This is the cell range in which the criterion will be checked against.
• criteria: The conditional criteria used to determine which cells will be added together.
• sum_range: The cells that would be added together. If it is not supplied in COUNTIF(), the cells in criteria_range are added together.

Now, without further ado, let's jump into an example that uses both SUMIF() and SUMIFS() to calculate the total revenue and the revenue per month generated by our fake sales representatives:

It is curious to note that, in the case of SUMIFS(), sum_range is a required argument and it is the first argument, but it is optional and the last argument in SUMIF(). This can cause confusion when you are using both formulas as you can see when I was typing the SUMIFS function for February. Therefore, I advise caution when you are working with both formulas. Be sure to always check that you are adding the correct cell ranges.

IFERROR()

For those of you that know a programming/scripting language, you know that there is such as thing as try/catch blocks for error handling. In Excel, we have the IFERROR() function to do exactly that. The IFERROR function allows the user to "catch" errors such as #N/A, #VALUE! or #REF!, and provide a more meaningful output. The syntax of the IRERROR function is as follows:

IFERROR(value, value_if_error)

Where:

• value: The value or formula to check for an error
• value_if_error: The value to output when the function encounters an error.

Let's see how it works in action in the following video:

When you are working with business sales reports, it is usually a good practice to cover certain calculations in IFERROR functions. At times, you will get situations when new salespeople will have very few appointments, qualified opportunities or closed deals, which can cause divisions by 0 when calculating things like the percentage of appointments that turn into qualified opportunities for a given sales person. This can output nasty-looking errors that will probably make your report look dirty and harder to read for decision makers. However, if you cover these calculations with IFERROR() statements, you can substitute those errors for something much nicer on the eyes like a dash, which would make your report more presentable. Furthermore, IFERROR() statements can go beyond just outputting nicer error messages. You can also nest these statements to perform things like chained VLOOKUPs.

Conclusion

Congratulations! You are now aware of several ways in which you can leverage conditionals in Excel. In this tutorial, I have endeavored to cover those functions that I see myself using often when I am tasked to complete Excel business reports. However, if you are still hungry to learn more about the topic, there are other functions out there such as SWITCH(), AVERAGEIF() or AVERAGEIFS() that I haven't talked about here, and that you may find of interest.

Furthermore, there are things like using wildcards in functions such as COUNTIFS() that are perfectly valid ways to count partial string matches. For instance, let's say you have an Excel file with a list of a restaurant's menu items, and you want to count how many of them mention the word "fish". In this case, you could write COUNTIF(cell_range,"*fish") and this function would count items like swordfish or dolphinfish. I strongly encourage you to learn more about wildcards as they can be very useful and you never know when they could come in handy in your next Excel project.

Lastly, you may also want to check out the Data Analysis with Spreadsheets course here at DataCamp since it has a section dedicated to conditional function, which may be of further reference.

Take a look at our if…elif…else in Python Tutorial.