Learn functions in families instead of memorizing a giant list. Use SUMIFS and COUNTIFS for conditional totals, IF for a small business rule, IFERROR for an expected lookup failure, text functions for cleanup, and date functions for reporting periods.
- Choose a function only after stating the result you need in plain language.
- Use criteria cells instead of typing changing assumptions inside formulas.
- Break a long formula into helper columns when that makes the logic easier to audit.
Match the function to the question
SUMIFS answers questions such as total revenue for the West region in June. COUNTIFS counts records that meet several conditions. AVERAGEIFS calculates a conditional average. These functions use a result range plus one or more criteria range and criteria pairs.
IF is useful for a compact rule, such as marking an invoice Late when its due date has passed and its paid date is blank. Deeply nested IF formulas are difficult to review. When a rule has many branches, a lookup table is often clearer.
Handle errors without hiding real problems
IFERROR can replace an expected error with useful text, but it catches every error type. Use it around a known failure point, such as a lookup that may not find a new product code. Do not wrap an entire model in IFERROR, because a broken reference and a missing lookup are not the same problem.
- TRIM removes extra spaces around words.
- TEXT converts a number or date to formatted text for a label.
- EOMONTH returns a month-end date and helps build reporting periods.
Total paid revenue for one region
Revenue is in D, Region is in B, Status is in E, and G2 contains the selected region.
- Write the question: sum Revenue where Region equals G2 and Status equals Paid.
- Enter =SUMIFS(D:D,B:B,G2,E:E,"Paid").
- Change G2 to another region and confirm that the total updates without editing the formula.
Common mistakes
- Using SUM when the question contains conditions.
- Hard-coding changing criteria in several formulas instead of referencing one cell.
- Wrapping every formula in IFERROR and hiding broken logic.
- Building a formula so long that nobody can explain each part.
Try one
Which function counts rows where Region is East and Status is Open?
COUNTIFS, because the count depends on two criteria ranges and two criteria.
Sources
- Microsoft Excel help and learningOfficial reference for Excel features, formulas, tables, charts, and data tools.