Conditional Calculations — AI-Assisted IF, SUMIFS, and COUNTIFS
Chapter 4: Conditional Functions
The leap from "sum everything" to "sum only what meets my criteria" transforms Excel from a calculator into a real data analysis tool. The conditional function family — IF, SUMIF, COUNTIF, AVERAGEIF, MAXIFS — is among the most-used in workplace Excel, and one of the highest-ROI scenarios for AI-assisted formula writing.
The Essence: From "All" to "Conditional"
Every conditional function follows the same underlying logic: find rows in the condition range that match your criteria, then perform a calculation on the corresponding rows in the calculation range.
- SUMIF / SUMIFS — match condition → sum
- COUNTIF / COUNTIFS — match condition → count
- AVERAGEIF / AVERAGEIFS — match condition → average
- MAXIFS / MINIFS — match condition → max or min value
- IF / IFS — match condition → return value A, else return value B
ℹ️ Note
Why AI excels here: Conditional requirements are naturally expressed in plain language ("total salary for the Sales department"). AI can translate that sentence directly into a formula — making this one of the highest-ROI scenarios for AI-assisted Excel work.
IF & IFS: Core Logic Functions
IF Syntax
=IF(condition, value_if_true, value_if_false)
Examples:
=IF(B2>=100000, "On Target", "Below Target")
=IF(A2>=90, "A", IF(A2>=80, "B", IF(A2>=70, "C", "F")))
IFS Syntax (Excel 2019+ / Microsoft 365)
=IFS(condition1, result1, condition2, result2, ..., TRUE, fallback)
Grade example:
=IFS(A2>=90,"A", A2>=80,"B", A2>=70,"C", TRUE,"F")
6 Workplace Cases
Case 1: Sales Target Check Column B is monthly sales (in dollars). Column C should show "On Target" if sales >= $100,000, otherwise "Below Target".
Write an IF formula to check if sales is on target.
Case 2: Grade Bands Column A has scores (0–100). Column B should show the letter grade: A (90+), B (80–89), C (70–79), F (below 70).
Case 3: Tiered Commission Column B is sales amount. Column C calculates commission: under $100K → 5%, $100K–$300K → 8%, $300K–$500K → 10%, $500K+ → 12%.
Case 4: Multi-Condition (Department + Level) Column A is department, Column B is job level. Column C should show "Eligible" only for Sales department employees at Manager level or above.
Case 5: IF + AND / OR Column A is attendance days, Column B is monthly rating (1–5). Bonus if: attendance >= 22 AND rating >= 4, OR rating = 5 (regardless of attendance).
Case 6: IFS for Multi-tier Lookup Column A has customer rating (1–5 stars). Column B should return discount rate: 5★→0.90, 4★→0.92, 3★→0.95, 2★→0.98, 1★→1.00.
SUMIF / SUMIFS: Conditional Summing
SUMIF (single condition):
=SUMIF(criteria_range, criteria, sum_range)
SUMIFS (multiple conditions) — note: sum_range comes FIRST:
=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)
⚠️ Warning
Common trap: SUMIF and SUMIFS have different parameter orders. In SUMIFS, the sum range is first. Mixing these up is one of the most common formula errors.
SUMIF Case 1: Departmental Payroll Total
SUMIF Case 2: Monthly Sales Total
SUMIF Case 3: Product Category + Region
SUMIF Case 4: Sum Values Above a Threshold
SUMIF Case 5: Wildcard / Fuzzy Match
COUNTIF / COUNTIFS: Conditional Counting
=COUNTIF(criteria_range, criteria)
=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2, ...)
COUNTIF Case 1: Headcount by Department
COUNTIF Case 2: Identify Duplicates
COUNTIF Case 3: Count Records in Date Range
COUNTIF Case 4: Unique Count
AVERAGEIF / AVERAGEIFS: Conditional Averaging
=AVERAGEIF(criteria_range, criteria, average_range)
=AVERAGEIFS(average_range, criteria_range1, criteria1, ...)
AVERAGEIF: Average Order Value by Region
AVERAGEIFS: Multi-condition Average (Region + Category)
MAXIFS / MINIFS: Conditional Max and Min
=MAXIFS(max_range, criteria_range1, criteria1, ...)
=MINIFS(min_range, criteria_range1, criteria1, ...)
Legacy Excel (array formula — press Ctrl+Shift+Enter):
{=MAX(IF(A2:A1000="Sales", C2:C1000))}
MAXIFS: Highest Salary per Department
MINIFS: Lowest Price per Product
Quick Reference & AI Prompt Templates
| Function | Purpose | Key Syntax Note | Min Version |
|---|---|---|---|
| IF | Single condition → two outcomes | =IF(cond, true_val, false_val) | All versions |
| IFS | Multi-condition (replaces nested IF) | =IFS(c1,v1,c2,v2,...,TRUE,fallback) | Excel 2019+ |
| SUMIF | Single-condition sum | =SUMIF(crit_range, crit, sum_range) | All versions |
| SUMIFS | Multi-condition sum | =SUMIFS(sum_range, crit_range1, crit1...) | Excel 2007+ |
| COUNTIF | Single-condition count | =COUNTIF(crit_range, crit) | All versions |
| COUNTIFS | Multi-condition count | =COUNTIFS(crit_range1, crit1...) | Excel 2007+ |
| AVERAGEIF | Single-condition average | =AVERAGEIF(crit_range, crit, avg_range) | Excel 2007+ |
| AVERAGEIFS | Multi-condition average | =AVERAGEIFS(avg_range, crit_range1, crit1...) | Excel 2007+ |
| MAXIFS | Multi-condition maximum | =MAXIFS(max_range, crit_range1, crit1...) | Excel 2019+ |
| MINIFS | Multi-condition minimum | =MINIFS(min_range, crit_range1, crit1...) | Excel 2019+ |
✅ Tip
Chapter Summary:
IF family handles "decision logic"; SUMIF/COUNTIF/etc. handle "conditional aggregation." Don't confuse them.
In multi-condition functions (SUMIFS, AVERAGEIFS, etc.), the calculation range comes first, then condition pairs.
Text criteria need quotes; numbers don't. Comparison operators (>, =) go inside quotes; cell references use & to join.
Wildcards * and ? work in text criteria for fuzzy matching.
For complex conditional formulas, describe your table structure and requirement to AI — it's far faster than trial and error.
Previous ← Chapter 3: Lookup & Reference Functions Next Chapter 5: Text Functions →