GUIDE: Logical & Math Functions (IF, COUNTIF, AVERAGEIF)

📘 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

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) </td