✦ What You’ll Learn
- ✓How COUNTIF, SUMIF, and AVERAGEIF share the same logic — and how to apply all three
- ✓Exact syntax for each function with argument-by-argument breakdowns
- ✓How to use wildcards, operators, and cell references in your criteria
- ✓When to upgrade to COUNTIFS, SUMIFS, and AVERAGEIFS for multiple conditions
- ✓How to fix the most common errors — including the zero-result trap
COUNTIF SUMIF AVERAGEIF in Excel are the three formulas that separate spreadsheet beginners from people who actually save hours every week. Most Excel users have a column of data and a question — “how many?”, “how much?”, “what’s the average?” — but only for some of the rows. These three functions answer exactly that, and once you understand the logic behind one, the others click into place almost automatically.
Think of them as a family: all three let you set a condition (a criterion), point Excel at a range, and get a single calculated result back. The only difference is what they calculate — a count, a sum, or an average. Master the pattern once, and you’ve effectively learned all three.
In this lesson, we’ll walk through each function with real-world examples, explain where people go wrong, and show you how to step up to the plural versions (COUNTIFS, SUMIFS, AVERAGEIFS) when you need to filter by more than one condition. Let’s get into it.
Why Conditional Formulas Change How You Work in Excel
A plain SUM adds everything in a range. That’s useful — but what if you only want to add the sales from the East region, or count how many students passed, or find the average score of employees in the Marketing department? You can’t do that with basic formulas alone. You need a condition baked into the formula itself.
The Difference Between SUM and SUMIF
SUM(A1:A100) adds every number in that range — no questions asked. SUMIF first checks each corresponding cell in a criteria range, and only adds the value if it passes your test. Same idea applies to COUNTIF (vs COUNT) and AVERAGEIF (vs AVERAGE).
One Shared Logic, Three Different Outputs
All three functions follow the same pattern: tell Excel where to look for the condition, what the condition is, and (if needed) where to find the values to calculate. COUNTIF counts qualifying rows. SUMIF adds qualifying values. AVERAGEIF averages them. Learn the pattern once — use it everywhere.
How the Excel COUNTIF Formula Works
COUNTIF is the simplest of the three. It scans a range and counts how many cells match your criteria. One range, one condition, one number back.
COUNTIF Syntax — Argument by Argument
=COUNTIF(range, criteria)
range — The cells you want Excel to check (e.g., A2:A100)
criteria — The condition to match: text, number, operator, or wildcard (e.g., “Passed”, “>500”, “East*”)
Counting Text, Numbers, and Dates
COUNTIF handles all three data types. For text, wrap your criteria in double quotes. For numbers, you can pass the number directly or use quotes with an operator. For dates, wrap the date in quotes or reference a cell containing the date.
Using Comparison Operators in Criteria
When you use operators like >, <, >=, <=, or <>, the entire criteria string must be in double quotes — even though it contains a number. This trips up a lot of people.
⚠️ COMMON MISTAKE
Writing =COUNTIF(C2:C100, >500) without quotes around the operator will return an error. Always wrap the entire operator-value string in quotes: ">500".
Wildcard Criteria: * and ? in COUNTIF
Wildcards let you match partial text:
*matches any number of characters —"East*"catches “East”, “Eastern”, “East Region”?matches exactly one character —"J?n"matches “Jan”, “Jun”, “Jon”"*apple*"matches any cell containing the word apple anywhere
How the Excel SUMIF Function Works
SUMIF adds a third argument to the pattern: where are the values you actually want to sum? This is the concept most beginners miss — your criteria column and your values column are often different columns entirely.
SUMIF Syntax — Why Argument Order Matters
=SUMIF(range, criteria, [sum_range])
range — The column Excel checks against your criteria (e.g., the Region column)
criteria — The condition to match (e.g., “East”)
sum_range — The column containing the values to add (e.g., the Revenue column) — optional if same as range
When Your Criteria Range and Sum Range Are Different Columns
This is the most important concept in SUMIF. Imagine column B holds regions (“East”, “West”) and column C holds revenue figures. You want to total all East revenue. Your criteria range is column B, your criteria is “East”, and your sum_range is column C. The formula looks like this:
=SUMIF(B2:B100, “East”, C2:C100)
Check column B for “East” → sum the corresponding values in column C
💡 PRO TIP
Instead of hardcoding “East” in your formula, reference a cell (e.g., F2) that contains the region name. This turns your SUMIF into a dynamic lookup — change the cell value and the formula updates instantly. Perfect for summary dashboards.
Building a Live Regional Summary Table with SUMIF
A sales manager can build a full summary table with one SUMIF per region. Lock the data ranges with absolute references ($B$2:$B$100) and reference the region names from a side column — then copy the formula down. The table updates live every time the data changes, no PivotTable required. For a deeper look at PivotTables, see our lesson on Pivot Tables in Excel.
How AVERAGEIF Excel Works (And How It Handles Blanks)
AVERAGEIF works exactly like SUMIF — but instead of adding qualifying values, it averages them. The syntax is identical, so if you’ve learned SUMIF, you’ve practically already learned AVERAGEIF.
AVERAGEIF Syntax and Key Differences
=AVERAGEIF(range, criteria, [average_range])
range — Where to check the condition
criteria — The condition to apply
average_range — The values to average (optional if same as range)
Averaging Only Values Above a Threshold
A teacher wants to know the average exam score of students who passed (scored 40 or above). A plain AVERAGE would pull down the result with failing scores. AVERAGEIF filters them out cleanly:
=AVERAGEIF(D2:D100, “>=40”, E2:E100)
Average exam scores (column E) only for students who scored 40+ in column D
How AVERAGEIF Treats Zeros vs Blank Cells
This is a subtle but important distinction. AVERAGEIF ignores blank cells in the average_range — they don’t count as zero, they don’t count at all. However, cells containing the value 0 are included in the average. If you have a student who scored 0 on an exam (as opposed to not sitting it), AVERAGEIF will include that 0 in the calculation.
⚠️ COMMON MISTAKE
If no cells match your AVERAGEIF criteria, the formula returns #DIV/0! — because it’s trying to average zero rows. Wrap it in IFERROR to handle this gracefully: =IFERROR(AVERAGEIF(...),"No data").
COUNTIF SUMIF AVERAGEIF in Excel vs Their Plural Versions
The singular functions handle one condition. The moment you need two or more conditions, you upgrade to the plural versions: COUNTIFS, SUMIFS, and AVERAGEIFS. The logic is the same — you just add more criteria pairs.
When to Upgrade from COUNTIF to COUNTIFS
If you want to count employees who are in the “Marketing” department and have been absent more than 3 days, you need COUNTIFS — two conditions, two criteria ranges.
=COUNTIFS(range1, criteria1, range2, criteria2, …)
Example:
=COUNTIFS(B2:B100,”Marketing”,C2:C100,”>3″)
SUMIFS — Adding a Second (or Third) Condition
Note the important syntax shift in SUMIFS: the sum_range comes first, then the criteria pairs. This is the reverse of SUMIF’s argument order — a common source of errors when switching between the two.
=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2)
Example — Total East region revenue above $500:
=SUMIFS(C2:C100, B2:B100,”East”, C2:C100,”>500″)
Side-by-Side Comparison: Singular vs Plural
Common Errors and How to Fix Them
Most COUNTIF, SUMIF, and AVERAGEIF problems come down to a handful of predictable mistakes. Here’s how to diagnose and fix them fast.
SUMIF / COUNTIF Returning 0 — The Text-vs-Number Trap
If your formula returns 0 when you know it should find matches, the most common culprit is numbers stored as text. Excel won’t match the number 500 to the text “500” stored in a cell. Check your data: select a suspect cell, look for the green triangle in the corner, and convert to numbers using the warning icon that appears.
⚠️ COMMON MISTAKE
Numbers imported from external systems (CSV files, ERP exports) often arrive as text. Your SUMIF will return 0 because the text “500” ≠ the number 500. Fix the data first, or use VALUE() to convert on the fly.
#VALUE! Errors and Mismatched Range Sizes
If your criteria_range and sum_range are different sizes (e.g., A2:A100 vs C2:C50), Excel will return a #VALUE! error. Always make sure both ranges span exactly the same number of rows.
Case Sensitivity: Why “Apple” and “apple” Both Match
COUNTIF and SUMIF are completely case-insensitive. “EAST”, “East”, and “east” will all match the same criteria. If you need case-sensitive matching, you’ll need to use SUMPRODUCT with EXACT — a more advanced technique covered in a later lesson.
3 Real-World Use Cases You Can Apply Today
Use Case 1: Sales Summary by Region (SUMIF)
A sales manager has a raw data sheet with hundreds of rows — each row is a transaction, with columns for Region, Salesperson, and Revenue. She wants a live summary table showing total revenue per region. She builds a small table on a separate area of the sheet, lists each region name in one column, and uses SUMIF in the next column to pull the total. Every time the raw data updates, the summary table refreshes automatically. No PivotTable rebuild required.
Use Case 2: HR Attendance Counter by Status (COUNTIFS)
An HR coordinator tracks attendance with a status column: “Present”, “Absent”, “Half-day”. She uses COUNTIF to count total absences for each employee across the month. When she wants to filter further — absences in the Marketing department only — she switches to COUNTIFS with a second condition on the Department column. This feeds directly into her monthly HR report. If you’re working with dates in your data, our lesson on Date & Time Functions in Excel will help you add date-based conditions to your COUNTIFS.
Use Case 3: Average Score Among Passing Students (AVERAGEIF)
A teacher has a column of student scores and wants the average score of only the students who passed (scored 40 or above). A plain AVERAGE would include failing scores and mislead the result. AVERAGEIF with criteria ">=40" excludes failing rows entirely, giving a true picture of how well the passing cohort performed.
Tips to Make Your Conditional Formulas More Flexible
Use Cell References Instead of Hardcoded Criteria
Replace hardcoded text like "East" with a cell reference like F2. This makes your formula dynamic: changing the cell content changes the formula’s behaviour. For operator criteria, concatenate: ">"&F2 uses the value in F2 as the threshold.
Combine with Named Ranges for Readability
Instead of B2:B100, name your range “Region” and use =SUMIF(Region,"East",Revenue). The formula becomes self-documenting — anyone reading it immediately understands what it does. Learn how in our guide to Named Ranges in Excel.
💡 PRO TIP
SUMIF and COUNTIF are ideal for fixed summary tables where you know exactly what to summarise. When you need to explore the data from multiple angles or don’t know which categories you’ll need, a PivotTable is faster and more flexible. Think of SUMIF as a precision tool and PivotTables as a power drill.
🧪 Try It Yourself
Open a blank Excel sheet and build this quick practice dataset:
- Column A: Region — East, West, North (repeat randomly across 20 rows)
- Column B: Sales Amount — random numbers between 200 and 1000
- Column C: Status — “Paid” or “Pending” for each row
Then try writing these formulas from scratch:
- Count how many rows are from the East region — COUNTIF
- Total sales for the West region — SUMIF
- Average sales amount for “Paid” transactions — AVERAGEIF
- Count East region rows that are also “Paid” — COUNTIFS
📋 Step-by-Step: Build a SUMIF Summary Table
- Set up your raw data with at least a Category column and a Values column
- In a separate area, list your unique category names (e.g., East, West, North) in a column
- In the adjacent column, type
=SUMIF($A$2:$A$100, F2, $B$2:$B$100)where F2 is the first category name - Lock your data ranges with
$signs so they don’t shift when you copy down - Copy the formula down for each remaining category
- Add new rows to your raw data — watch the summary table update instantly
Frequently Asked Questions
📌 Quick Reference — What Do These Functions Do?
COUNTIF(range, criteria) — Counts cells in a range that match a single condition.
Example: =COUNTIF(A2:A100,”Passed”)
SUMIF(range, criteria, [sum_range]) — Adds values where a corresponding cell meets your condition.
Example: =SUMIF(B2:B100,”East”,C2:C100)
AVERAGEIF(range, criteria, [average_range]) — Returns the average of values that meet a single condition.
Example: =AVERAGEIF(D2:D100,”>=40″,E2:E100)
What is the difference between COUNTIF and COUNTIFS?
COUNTIF handles a single condition. COUNTIFS handles two or more conditions simultaneously — each condition gets its own range-criteria pair. If you only have one condition, either works, but COUNTIFS is technically more flexible.
Can SUMIF handle multiple criteria?
No — SUMIF only supports one condition. To filter by two or more criteria, use SUMIFS. Note that in SUMIFS, the sum_range argument comes first, which is the opposite of SUMIF’s argument order.
Why does my AVERAGEIF return #DIV/0!?
This happens when no cells in the range match your criteria — Excel can’t average zero values. Use IFERROR(AVERAGEIF(...),"No data") to display a friendly message instead.
Is COUNTIF case-sensitive in Excel?
No. COUNTIF, SUMIF, and AVERAGEIF are all case-insensitive. “EAST”, “East”, and “east” will all be treated as the same value.
Can I use SUMIF across multiple sheets?
SUMIF does not natively support multi-sheet 3D references. A common workaround is to use SUMPRODUCT combined with INDIRECT, or to consolidate your data onto one sheet first. For large multi-sheet workbooks, a PivotTable connected to a data model is usually the cleaner solution.
📚 External Resources
← Previous Lesson
Date & Time Functions in ExcelNext Lesson →
Pivot Tables in Excel