Whether you're looking to start using the Formula Column for the first time, or you're making your way to becoming a Formula Column expert, chances are the IF function is the place you will go. With the IF function's nearly endless useful capabilities, it's no wonder that it is the most frequently used function in the Formula Column! 

In this article, we'll first learn all about the basics of the IF function and then we'll explore other variations and conditions which can be added to it as well. Read on to learn all about what the IF function is, how it works, and some examples on how to use it. 🙌

 

Understanding the IF function

If you're new to the formula world, you may be wondering "What is the IF function?" Well, in essence, the IF Function is a type of formula which is used to create a conditional statement and it will return, or output, a value depending on whether the set condition is met or not.

In its simplest form, an IF Function formula is structured as follows: 

1. First, the condition (ex: IF({Status}="Done")
2. Then, the value returned if the condition is met (ex: "YAY!"). 
3. Finally, the value returned if the condition is not met (ex: "Not yet..."))

When combined into one formula, it will look like so:

 

Let's break it down!

In the example shown above, the formula means that if the status is set to "Done", then it will return the value of "YAY!". On the other hand, if the status is not set to "Done", then it will return the value of "Not yet...". Therefore, the result of this formula is conditional upon whether or not the status has the label of "Done".

 

This is what it will look like when placed on a board! ⬇️

 

Simple IF function use-case

Now that we understand what the IF function is, and how it is structured in its most basic form, let's look into a budget tracking use case to see how the IF function can be used in a real life example! 

The board above is used to track the budget of an organization. Thanks to the Formula Column, we can see at a glance how the actual amount of money spent compares to the budget set. The following formula is used here: 

 

With this function in place, the Formula Column will read if the "Budgeted" value is less than the "Actual Spending" value. If the "Budgeted" value is indeed less than the "Actual Spending" value, then the text "Over Budget" will be displayed. Otherwise, if this condition is not met and the "Budgeted" value is more than the "Actual Spending", then the text "Under Budget" will be shown instead.

 

Using AND/OR conditions with the IF Function

When using the IF Function, you may need to specify additional conditions. This will be particularly important as you are looking to build longer and more complex calculations! For this reason, other logical functions such as AND or OR can be combined with the IF function.

As shown above, when an additional condition or function is added to an IF formula, the additional statement (i.e. AND({Total Sales}>350000,{Deals}>12)) will be enclosed within the initial IF statement. Let's dive further into an AND condition example, followed by an OR condition example, below to see how this works!

 

AND condition

The AND function can be used within an IF formula to allow you to test for multiple conditions in order for a value to be displayed. When using this, all conditions placed must be met in order for a value to be shown. If only one or none of these conditions are met, the value placed in the formula that is defined for conditions that are not met will be shown instead. 

Let's consider the below Sales Lead Management board to see an example of how the IF and AND functions can be combined to determine if a $2,500 bonus should be received or not:

On this board, we've added a Formula Column titled "Bonus value" to display the $2,500 bonus amount that a Sales Rep may be eligible to receive. In order for a Sales Rep to be entitled to receive the $2,500 bonus, the deal size must be over $350,000 and the payment status must be marked as "Paid". The following formula can allow us to perform just that: 

 

The formula example above will work as so: 

• The formula will first read whether the Deal size is over 350000 and the Payment status column is marked as "Paid"
• If both of these conditions are met, the value of "$2500" will be returned.
• Otherwise, if either of these conditions are not met, then the formula will return the value of "$0".

Great! Thanks to the combination of the AND and IF function, our formula can help us easily determine whether a Sales Rep is qualified for a $2,500 bonus or not! 💥

 

OR condition

In a similar fashion to the AND condition, the OR function also allows you to test for multiple different conditions in order for a value to be displayed. The main difference between the two, is that for the OR condition, only one of the conditions placed must be met in order for a value to be shown.

 

To see this in action, let's consider the Sales Team Commissions board below! Similar to the previous use case, this board allows us to calculate a bonus rate for each of our sales reps. However, this time, the formula column (titled "Bonus rate") considers other variables when performing this calculation. 

 

This time, in order for a sales rep to be considered for a bonus, only one of two conditions must be met:

• Either their Total Sales amount has to be higher or equal to (>=) the Sales Goal
• or their number of accounts must be greater than or equal to (>=) the Account Goal

In order to achieve this calculation, the formula will look as so:

 

If either of the two conditions are met (Total sales >= Sales goal or # accounts >= Account goal), then the sales rep will be qualified for a bonus which will be calculated by multiplying their Total Sales value by the Commission rate. Otherwise, if neither of these conditions are met, their Bonus rate will be $0.

 

If you have any questions, please reach out to our team right here. We’re available 24/7 and happy to help.