Counting and adding with conditions: COUNTIF, SUMIF and friends
Answer business questions with COUNTIF, SUMIF, AVERAGEIF and their multi-condition versions, with real, independently checked results.
In this lesson you will
- Use COUNTIF, SUMIF and AVERAGEIF
- Use COUNTIFS and SUMIFS with several conditions
- Write conditions with operators, wildcards and cells
- Total by month
About 15 minutes
The question every report asks
Anita manages a small stationery business and has a sheet with twelve sales: date, region, product, units, price and revenue. Her owner keeps asking questions: ‘How much did North sell? How many notebook sales in the East? What was the average North sale? What did we make in March?’ She could filter and read off totals, but then she must redo it for every new question. The family of IF functions answers each one with a single formula that updates by itself.
The six functions
| Function | Job |
|---|---|
| COUNTIF | Counts cells that meet one condition |
| SUMIF | Adds values where a condition is met |
| AVERAGEIF | Averages values where a condition is met |
| COUNTIFS | Counts with several conditions |
| SUMIFS | Adds with several conditions |
| AVERAGEIFS | Averages with several conditions |
The pattern
For the singular versions the order is range to test, condition, range to add. For example =
Real results
The twelve sales have total revenue of 4160. These formulas were really calculated on the sheet and then checked with an independent Python calculation, and all of them matched:
| Formula | Question | Result |
|---|---|---|
| = |
Sales rows in North | 4 |
| = |
North revenue | 1340 |
| = |
Average North sale | 335 |
| = |
East notebook sales | 2 |
| = |
South notebook revenue | 540 |
| = |
Revenue of sales with 10+ units | 1640 |
| = |
Sales above 500 | 1 |
| = |
Sales rows with a region ending th | 7 |
Writing conditions
| Want | Condition |
|---|---|
| Exactly North | “North” or a cell like H2 |
| Not North | “< |
| Greater than 500 | “>500” |
| Greater than a cell value | “>”&H2 |
| Begins with N | “N*” |
| Contains no | “*no*” |
The wildcard * stands for any run of characters and ? for any single character. In the test, =
Totals by month
Dates need two conditions, a start and an end. For March 2026 the formula was =
Better than typing the word
Put the region name in a cell, such as H2, and write =
Common problems
- Ranges of different sizes in SUMIFS cause #VALUE!. Make them the same length.
- A condition that looks right but returns 0: check for extra spaces or numbers stored as text.
- Quotes are needed around text and operators; cell references are not quoted.
- Whole-column ranges such as B:B are fine and grow with the data, but keep them out of the column you are summing.
Practice
Use a list of at least twelve rows of your own (expenses by category, sales by city, marks by class). Build a small summary table: one row per category with a count, a total and an average, all using one formula each with the category read from a cell. Then add a second condition, such as a month or a minimum amount, using the IFS versions.
Check that the totals in your summary add up to the total of the whole list. If the summary total is smaller, a category is spelled slightly differently in the data or has a trailing space. Use TRIM or Find and Replace to fix it.
Mixing up the argument order. SUMIF puts the range to add last, but SUMIFS puts it first. If your SUMIFS returns 0 or an error, check the order.
Building a mini report from one formula
List the regions down column H: North, South, East and West. In column I write =
When the IF family is not enough
These functions answer questions about totals, counts and averages. If you need the biggest sale in one region, use MAXIFS (Excel 2019 and later). If you need a table of results by two things at once, such as region by month, a PivotTable (lesson 18) does it without any formulas. Knowing when to switch tools is part of working like a professional.
Check what you learned
Five quick questions. Pick an answer and you will see straight away whether it is right and why.
Which function adds values that meet one condition?
- COUNT
- SUMIF
- ROUND
- LEN
SUMIF adds matching rows.
In SUMIFS, which argument comes first?
- The condition
- The criteria range
- The range to add
- The result cell
SUMIFS starts with the sum range.
What does the * wildcard mean?
- Any run of characters
- Multiply
- Exactly one letter
- A date
N* matches North.
How do you write ‘greater than the value in H2’?
- “>H2”
- >H2
- “>”&H2
- H2>
Join the operator and cell with an ampersand.
Why use a cell for the condition instead of typing the word?
- One formula works for any value
- It is faster to type
- Excel requires it
- It changes colours
Change the cell and the answer follows.
Summary
- The IF family answers ‘how many, how much, what average’ with conditions.
- SUMIF puts the sum range last; SUMIFS puts it first.
- Write conditions with quotes, operators, wildcards and cell links.
- Use start and end dates for monthly totals.
Questions
What is the difference between COUNTIF and COUNTIFS?
COUNTIF takes one condition. COUNTIFS takes several pairs of range and condition and counts rows meeting all of them.
Can SUMIF use OR logic?
Add two SUMIFs together, or use SUM with an array of criteria such as SUM(SUMIF(B2:B13,{"North","South"},F2:F13)).
Do these work in Google Sheets?
Yes. The IF family has the same names and argument order in Google Sheets.
