📘 Guide: Essential Excel Logical & Math Functions
While SUMIF allows you to sum values based on a condition, real-world data analysis requires a complete toolkit of logical and conditional math functions. This guide covers IF, Nested IF / IFS, IFERROR, COUNTIF, COUNTIFS, AVERAGEIF, and core summary formulas.
1. The IF Function & Nested IFs (3-Tier Logic)
The IF() function tests whether a condition is true or false, returning one value if TRUE and another if FALSE.
=IF(logical_test, value_if_true, value_if_false)
Nested IF / IFS for Multiple Conditions
When you have 3 or more categories (e.g. "OVERPRICED", "OK", "DISCOUNTED"), nest additional IF statements or use the IFS() function:
=IF(C2 > 100, "OVERPRICED", IF(C2 >= 50, "OK", "DISCOUNTED")) =IFS(C2 > 100, "OVERPRICED", C2 >= 50, "OK", C2 < 50, "DISCOUNTED")
2. Clean Error Handling with IFERROR
Prevent ugly #N/A or #DIV/0! errors from breaking your dashboards using IFERROR():
=IFERROR(VLOOKUP(A2, Data!A:B, 2, FALSE), "Not Found")
3. COUNTIF & COUNTIFS — Conditional Counting
Use COUNTIF() to count rows that satisfy a single condition, and COUNTIFS() for multiple conditions.
=COUNTIF(range, criteria) =COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2)
Examples:
- Count all orders from the "East" region:
=COUNTIF(B2:B100, "East") - Count high-value orders in the "East" region:
=COUNTIFS(B2:B100, "East", C2:C100, ">=500")
4. Summary Reference Cheat Sheet
| Function | Purpose | Syntax Example |
|---|---|---|
IF / IFS |
Returns values based on True/False tests | =IF(A2>0, "Pass", "Fail") |
IFERROR |
Replaces errors with custom text/values | =IFERROR(VLOOKUP(...), "N/A") |
SUMIF |
Sums values matching 1 criteria | =SUMIF(Region, "East", Sales) |
COUNTIF |
Counts cells matching 1 criteria | =COUNTIF(Status, "Completed") |
AVERAGEIF |
Averages cells matching 1 criteria | =AVERAGEIF(Tier, "VIP", Spend) |