If new to Excel and building formulas, there’s a concept I think is very important to understand before learning to build formulas using IF, SUMIF, COUNTIF, etc.. It’s called Boolean Logic.

Don’t let the name throw you! Underneath the hood of this concept is the simple reality that all you’re doing is building statements that equate to either TRUE or FALSE. 1 or 0. Yes or No. Based on which outcome you arrive at, your equation will do option 1 (TRUE) or option 2 (FALSE). Option 1 or Option 2 is what we’re trying to calculate, but we have to first figure out whether the statement is TRUE or FALSE.

IF() FORMULA

Format: IF(TEST, Value if True, Value if False)

Example:

Above we have the Q4 2016 data for every sales employee in the company. The CEO has decided that if an employee has had over 40 sales in one month, he/she gets a bonus. Let’s build an IF statement that determines which employees are eligible.

The first thing to check before crafting your IF statement is the Boolean Logic driving your formula. We’ve created a new column that checks if the value in column D is greater than 40. If TRUE, the formula returns “TRUE”. Otherwise, it returns “FALSE.”

Now that we see our formula is working, we can craft an IF statement that will return specific values that depend on the Boolean value of the original statement.

Let’s look back to the format of the IF() function:

IF(TEST, Value if True, Value if False)

In the example above, we’re testing whether or not the number of sales is greater than 40. If it is, we return the string “BONUS”. If the number of sales is less than or equal to 40, we return the value “Work Harder.”