SUMIF, COUNTIF, AVERAGEIF
SUMIF, COUNTIF, and AVERAGEIF let you total, count, and average a subset of data based on a single condition — without building a PivotTable. This lesson walks through the exact syntax of each, shows common criteria patterns including wildcards and comparison operators, and previews when you need to graduate to their multi-condition cousins, SUMIFS, COUNTIFS, and AVERAGEIFS.
Why this skill matters professionally
Conditional aggregation is one of the most frequently used formula patterns in bookkeeping, sales reporting, and operations — 'total sales for the West region,' 'count how many invoices are overdue,' 'average order size for repeat customers.' Being fluent in these functions means you can answer a manager's ad-hoc question in seconds instead of building a whole PivotTable, which matters in fast-paced roles like AP/AR clerk, sales operations, and inventory analyst where quick, accurate slicing of data is a daily expectation.
Learning objectives
- Total, count, and average subsets of data using a single criterion.
Background concepts
The shared syntax pattern
All three functions follow the same logical shape: a range to evaluate the condition against, the condition itself, and (except for COUNTIF) a separate range to actually total or average.
=SUMIF(range, criteria, sum_range) =COUNTIF(range, criteria) =AVERAGEIF(range, criteria, average_range)
Criteria can be text, numbers, or operators
Criteria is the most flexible — and trickiest — part. It accepts exact text ("West"), a number (100), a comparison (">100"), a cell reference, or a wildcard pattern.
- "West" — exact text match
- ">100" — numeric comparison, must be in quotes
- "*inc*" — wildcard, matches any text containing 'inc'
- "<>"&B2 — not equal to whatever is in B2
Range and sum_range must be the same shape
The criteria range and the sum/average range must have the same number of rows. If your criteria column is B2:B500, your sum range must also be 499 rows, like D2:D500 — mismatched ranges either error or silently misalign.
When one condition isn't enough
The moment you need two or more conditions at once — like Region = West AND Month = March — you move to SUMIFS, COUNTIFS, or AVERAGEIFS, which accept repeating range/criteria pairs.
Step-by-step
1. 1. Write a basic SUMIF
=SUMIF(B:B,"West",D:D) scans column B for 'West' and sums the corresponding row in column D. Using whole-column references (B:B) means new rows are automatically included.
2. 2. Write a basic COUNTIF
=COUNTIF(B:B,"West") counts how many rows have 'West' in column B — no separate sum range needed since you're just counting matches.
3. 3. Write a basic AVERAGEIF
=AVERAGEIF(B:B,"West",D:D) averages the amounts in D for rows where B says West, ignoring all other rows entirely.
4. 4. Use a comparison operator as criteria
=COUNTIF(D:D,">1000") counts how many amounts exceed 1000. Note the operator and number must be combined inside one set of quotes.
5. 5. Reference a cell inside criteria
To make criteria dynamic, concatenate an operator with a cell reference: =COUNTIF(D:D,">"&F1) counts values greater than whatever threshold is typed in F1, so users can change F1 without editing the formula.
6. 6. Use wildcards for partial text matches
=SUMIF(C:C,"*Inc*",D:D) sums amounts for any company name containing 'Inc' anywhere in the text — useful when naming isn't perfectly consistent.
7. 7. Count blanks or non-blanks
=COUNTIF(B:B,"") counts blank cells; =COUNTIF(B:B,"<>") counts non-blank cells — handy for finding incomplete records.
8. 8. Graduate to multiple conditions
When you need Region = West AND Quarter = Q1, use SUMIFS instead — note the sum_range moves to the first argument.
=SUMIFS(D:D, B:B,"West", C:C,"Q1")
Real-world workplace examples
Regional sales rollup
=SUMIF(Region,"West",Sales) gives a manager the West region's total instantly without filtering the whole sheet.
Overdue invoice count
=COUNTIF(DueDate,"<"&TODAY()) counts every invoice whose due date has already passed.
Average deal size by rep
=AVERAGEIF(RepName,"Jordan",DealAmount) shows one rep's average deal size for a quick performance check.
Low-stock alert count
=COUNTIF(QtyOnHand,"<10") tells a warehouse manager how many SKUs need reordering at a glance.
Vendor spend by partial name match
=SUMIF(VendorName,"*Office*",Amount) totals spend across several vendors whose names all contain 'Office', even if punctuation varies.
Practical scenarios
Dynamic threshold dashboard
A finance analyst builds a dashboard where cell F1 holds an adjustable 'large expense' threshold. Instead of hardcoding =COUNTIF(D:D,">5000"), she writes =COUNTIF(D:D,">"&F1), so leadership can type any number into F1 — 5000, 10000, 2500 — and instantly see the count update without anyone editing formulas. This turns a static report into an interactive tool with almost no extra effort.
Criteria range mismatch bug
A bookkeeper writes =SUMIF(B2:B500,"Rent",D2:D600) — the sum range is 100 rows longer than the criteria range by mistake, likely from copy-pasting a formula from a different report. Excel doesn't error, but the totals are quietly wrong. Auditing the formula shows the ranges misaligned by one column, and realigning both to B2:B500 and D2:D500 fixes the total.
Common mistakes beginners make
Mismatched range sizes
Criteria range and sum range don't have equal row counts. Fix: always select ranges by dragging together, or use whole-column references (B:B, D:D) to guarantee equal length.
Forgetting quotes around operators
Writing =COUNTIF(D:D,>100) instead of =COUNTIF(D:D,">100") throws a formula error. Fix: operators combined with numbers must always be wrapped in quotation marks.
Using SUMIF when multiple conditions are needed
Trying to cram two conditions into one SUMIF's single criteria argument doesn't work. Fix: switch to SUMIFS as soon as a second condition is needed.
Case-sensitivity assumptions
COUNTIF/SUMIF are not case-sensitive by default, so 'west' and 'West' are treated the same — this can hide data entry inconsistencies. Fix: use EXACT() in a helper column if case must be distinguished.
Wildcard confusion
Forgetting that * matches any number of characters (not just one) leads to overly broad matches, like "*a*" matching almost everything. Fix: be as specific as possible in wildcard patterns.
Best practices
- Use whole-column references (B:B) for ranges that will keep growing, to avoid resizing formulas later.
- Combine operators with cell references (">"&F1) to make thresholds adjustable without editing formulas.
- Double check criteria range and sum range always match in size before trusting the result.
- Use SUMIFS/COUNTIFS/AVERAGEIFS proactively once you suspect a second filter condition is coming.
- Test formulas against a manually counted small sample to confirm logic before scaling to the full dataset.
- Keep criteria values in a labeled cell (like a threshold or region name) rather than buried inside the formula text.
Professional tips
- SUMIFS/COUNTIFS/AVERAGEIFS put the sum/average range FIRST, unlike the single-condition versions — a common source of transposed-argument errors when upgrading a formula.
- COUNTIFS with the same range twice can test a between-range condition, e.g. =COUNTIFS(D:D,">=100",D:D,"<=500").
- You can use array-like OR logic by adding two SUMIFs together: =SUMIF(B:B,"West",D:D)+SUMIF(B:B,"East",D:D).
- Named ranges make long-column SUMIF formulas far more readable than raw column letters.
- COUNTIFS can check across entirely different sheets as long as the ranges line up row for row.
Practice exercises
Regional totals
Build a 40-row sales list with Region and Amount columns, then write SUMIF formulas for each region and confirm they add up to the grand total.
Dynamic threshold counter
Add a threshold input cell and write a COUNTIF formula referencing it with a comparison operator.
Wildcard vendor search
Create a vendor name column with inconsistent naming (Office Depot, Office Depot Inc, OFFICE DEPOT #4) and write one SUMIF with a wildcard that captures all variants.
Upgrade to SUMIFS
Take your regional SUMIF and add a second condition (like Quarter) by converting it to SUMIFS.
Review questions
What three arguments does SUMIF take?
The range to check criteria against, the criteria itself, and the range to sum from (the sum_range).
Why doesn't COUNTIF need a sum_range?
Because it's only counting how many cells match the criteria, not totaling a separate column of numbers.
How do you write criteria for 'greater than 500'?
">500" — as a text string with quotes, since the operator and number are combined.
What breaks when the criteria range and sum range are different sizes?
The results become unreliable or Excel throws an error, because it can't reliably line up which sum cell corresponds to which criteria cell.
When should you move from SUMIF to SUMIFS?
As soon as you need to filter by more than one condition at the same time.
Key takeaways
- SUMIF, COUNTIF, and AVERAGEIF all follow range-criteria(-aggregate range) syntax.
- Criteria combining an operator and number must be wrapped in quotes, like ">100".
- Wildcards (*) let you match partial text.
- Criteria and sum ranges must always be the same size.
- Combine operators with a cell reference to build adjustable, dynamic thresholds.
- Move to SUMIFS/COUNTIFS/AVERAGEIFS the moment a second condition is needed.
- Whole-column references future-proof formulas against growing datasets.
Frequently asked questions
Are these functions case-sensitive?
No, they treat 'West' and 'west' identically.
Can criteria reference another cell directly?
Yes, e.g. =SUMIF(B:B,F1,D:D) where F1 holds the text or number to match.
What does COUNTIF(range,"<>") do?
It counts all non-blank cells in the range.
Can I use OR logic in a single SUMIF?
Not directly — add two SUMIF formulas together, or use SUMIFS combined with SUMPRODUCT for more complex OR logic.
Do these functions work across multiple sheets?
COUNTIFS/SUMIFS can reference ranges on other sheets as long as row alignment matches; SUMIF/COUNTIF/AVERAGEIF work the same way with single-sheet ranges.
What happens if no rows match the criteria?
SUMIF and COUNTIF return 0; AVERAGEIF returns a #DIV/0! error since there's nothing to average.
Is there a performance concern with whole-column references?
On very large workbooks it can slow recalculation slightly, but for most business files it's negligible and worth the flexibility.
Related lessons
12 min • Beginner
15 min • Easy
20 min • Intermediate
20 min • Advanced