The IF function and its various derivatives (like SUMIF, COUNTIF, AVERAGEIF, and more) are essential to anyone working with data in Excel. Whether you’re an analyst, project manager, or someone who regularly deals with Excel, mastering these formulas will help you quickly make data-driven decisions.

Let’s explore the IF family of functions, covering the basics and advanced examples.

The most common use of the IF function is to compare a value and what you expect logically. This function can return one result if the condition is TRUE and another if the condition is FALSE.

Formula: =IF(logical_test, value_if_true, value_if_false)

Example 1:

Check if a score is passing or failing: =IF(A1 >= 40, “Pass”, “Fail”)

Example 2:

In logistics, you might want to flag deliveries as “On Time” or “Late” based on the actual delivery date compared to the target date: =IF(B1 <= C1, “On Time”, “Late”)

The IFERROR function handles errors that formulas might return, such as #DIV/0!, #N/A, etc. This formula ensures your spreadsheet doesn’t show an error message, replacing it with a more user-friendly result.

Formula: =IFERROR(value, value_if_error)

Example:

If you are dividing two columns of numbers and want to avoid the #DIV/0! error, you can use: =IFERROR(A1/B1, “Error”)

This will display “Error” if there’s a division by zero or another issue.

As an alternative to Nested IF formulas, Excel introduced the IFS function to test multiple conditions more simply.

Formula: =IFS(logical_test1, value_if_true1, logical_test2, value_if_true2, …)

Example:

Categorize sales performance: =IFS(A1 > 1000, “Excellent”, A1 >= 500, “Good”, A1 < 500, “Poor”)

The SUMIF function sums the values in a range that meet a single condition, whereas SUMIFS allows for multiple conditions.

SUMIF Formula: =SUMIF(range, criteria, [sum_range])

SUMIFS Formula: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Example 1 (SUMIF):

Sum the sales for “Product A”: =SUMIF(A:A, “Product A”, B:B)

Example 2 (SUMIFS):

Sum the sales for “Product A” sold in “Mumbai”: =SUMIFS(B:B, A:A, “Product A”, C:C, “Mumbai”)

The COUNTIF function counts the number of cells that meet a single condition, while COUNTIFS can handle multiple conditions.

COUNTIF Formula: =COUNTIF(range, criteria)

COUNTIFS Formula: =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], …)

Example 1 (COUNTIF):

Count how many times “Product A” appears in a column: =COUNTIF(A:A, “Product A”)

Example 2 (COUNTIFS):

Count how many times “Product A” was sold by “John”: =COUNTIFS(A:A, “Product A”, C:C, “John”)

The AVERAGEIF function returns the range average based on a single condition, while AVERAGEIFS works for multiple conditions.

AVERAGEIF Formula: =AVERAGEIF(range, criteria, [average_range])

AVERAGEIFS Formula: =AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Example 1 (AVERAGEIF):

Find the average sales for “Product A”: =AVERAGEIF(A:A, “Product A”, B:B)

Example 2 (AVERAGEIFS):

Find the average sales for “Product A” sold by “John”: =AVERAGEIFS(B:B, A:A, “Product A”, C:C, “John”)

The Nested IF function tests multiple conditions within a single formula. This is useful when you have more than two possible outcomes.

Example:

Classify delivery times: =IF(A1 <= 1, “Fast”, IF(A1 <= 3, “On Time”, “Late”))

In this formula, if the delivery time is 1 day or less, it is categorised as “Fast,” between 1 and 3 days is considered “On Time,” and more than 3 days is “Late.”

You can combine IF with the AND or OR functions to handle multiple conditions in a logical test.

IF with AND Formula: =IF(AND(condition1, condition2, …), value_if_true, value_if_false)

IF with OR Formula: =IF(OR(condition1, condition2, …), value_if_true, value_if_false)

Example 1 (IF with AND):

Check if a delivery is “On Time” if both product quantity and time are within limits: =IF(AND(A1 <= 100, B1 <= 3), “On Time”, “Late”)

Here, A1 is the quantity (should be 100 or less), and B1 is the delivery time (3 days or less).

Example 2 (IF with OR):

Check if a shipment is “Urgent” if either quantity exceeds 500 or delivery time exceeds 5 days: =IF(OR(A1 > 500, B1 > 5), “Urgent”, “Standard”)

The SWITCH function is an alternative to Nested IF formulas when you want to match one value against multiple cases.

SWITCH Formula: =SWITCH(expression, value1, result1, [value2, result2], …, [default])

Example:

Assign a label based on a score: =SWITCH(A1, 1, “Low”, 2, “Medium”, 3, “High”, “Unknown”)

If A1 contains 1, the result will be “Low,” and so on.

The CHOOSE function selects a value or action to perform from a list based on an index number.

CHOOSE Formula =CHOOSE(index_num, value1, value2, …)

Example:

Choose the day of the week based on a number (1 to 7): =CHOOSE(A1, “Sunday”, “Monday”, “Tuesday”, “Wednesday”, “Thursday”, “Friday”, “Saturday”)

You can also combine the IF function with wildcards to match patterns in text-based data.

Example:

If you want to check if a cell contains the word “Complete,” you can use: =IF(COUNTIF(A1, “*Complete*”), “Yes”, “No”)

For more advanced users, IF can be combined with array formulas to perform calculations over ranges.

Example:

Calculate a bonus based on sales exceeding different thresholds: =IF(A1 > 1000, 0.10*A1, IF(A1 > 500, 0.05*A1, 0))

This formula checks if sales exceed 1000 for a 10% bonus; otherwise, it checks if sales exceed 500 for a 5% bonus. If neither is true, no bonus is awarded.

Excel’s IF formulas and their derivatives offer immense flexibility in making logical decisions, performing calculations, and managing complex datasets. These functions are foundational to efficient Excel workflows, from the simple IF formula to more advanced variants like SUMIF, COUNTIFS, SWITCH, and CHOOSE. Mastering them allows you to automate decisions, manage data dynamically, and draw insights that might otherwise be missed.

Understanding these formulas will enhance your productivity and analytical capabilities, whether a beginner or an advanced user. So, dig into Excel, explore these functions, and watch your efficiency skyrocket!