SUMIF and SUMIFS practice, with answers
Eight exercises on one table, with answers: one condition, two, over a cell value, a date range and wildcards, plus the argument order people get wrong.
SUMIF adds the numbers that pass one test. SUMIFS adds the numbers that pass several. They are the two functions that turn a list of rows into a report.
Their arguments are in a different order from each other. That is the single thing that makes them hard. Exercise 3 is where that shows up.
Put this table in A1 to D7 and write your answers in column F.
| Row | A | B | C | D |
|---|---|---|---|---|
| 1 | Date | Region | Rep | Amount |
| 2 | 2026-01-05 | North | Ana | 1200 |
| 3 | 2026-01-19 | South | Bo | 900 |
| 4 | 2026-02-02 | North | Ana | 1400 |
| 5 | 2026-02-16 | East | Cem | 1500 |
| 6 | 2026-03-03 | South | Bo | 1100 |
| 7 | 2026-03-21 | North | Dita | 800 |
Exercise 1 — one condition
Total everything sold in the North.
Answer: =SUMIF(B2:B7,"North",D2:D7) →
3400
Where to look. What to look for. What to add. The range being tested and the range being added are different columns. They must be the same height.
Exercise 2 — point at a cell, not at a word
Put North in F1. Total the North sales by reading F1.
Answer: =SUMIF(B2:B7,F1,D2:D7) →
3400
Change F1 to South and the answer becomes 2000. You do not touch the formula. Build every report this way: the words go in cells, the formulas read the cells.
Exercise 3 — two conditions, and the order changes
Total what Ana sold in the North.
Answer:
=SUMIFS(D2:D7,B2:B7,"North",C2:C7,"Ana") → 2600
Look at what moved. In SUMIF the range to add is last. In SUMIFS it is first.
There is a reason. SUMIFS takes any number of condition pairs. The pairs have to come at the end, where they can keep going. It is still the thing everybody gets wrong once.
Exercise 4 — bigger than a number
Total the sales over 1000.
Answer: =SUMIF(D2:D7,">1000") →
5200
Two things here. The test goes in quotation marks, operator and all. And there is no third argument. When the column you test is the column you add, leave it out.
Exercise 5 — bigger than whatever is in a cell
Put 1000 in F2. Total every sale above the number in F2.
Answer: =SUMIF(D2:D7,">"&F2) →
5200
This is the shape nobody guesses. The operator is text, so it goes in quotation marks. The cell is not text, so it goes outside them. The ampersand glues the two into one condition.
">F2" does not work. It looks for sales greater than the letters F2. That is not a number, so the answer is 0 and there is no error.
Exercise 6 — a date range
Total the sales from February and March.
Answer:
=SUMIFS(D2:D7,A2:A7,">=2026-02-01",A2:A7,"<=2026-03-31") →
3400
The same column is tested twice, once for each end of the range. That is normal and it is what SUMIFS is for.
Safer in a shared file: put the two dates in cells. Then write
">="&F3. A date inside a formula is read with the settings of the computer opening the file. In two countries, 03/04 means two different days.
Exercise 7 — starts with
Total the sales by every rep whose name starts with A or B.
Answer:
=SUMIF(C2:C7,"A*",D2:D7)+SUMIF(C2:C7,"B*",D2:D7) →
4600
The star matches any characters after the letter. A question mark matches exactly one. Both work on text only, never on numbers or dates.
Two SUMIFs added together, because one SUMIF takes one condition. SUMIFS would not help here. Its conditions are joined with AND, and no name starts with both A and B.
Exercise 8 — count and average, same shape
How many North sales were there, and what was the average?
Answer: =COUNTIF(B2:B7,"North") →
3
Answer: =AVERAGEIF(B2:B7,"North",D2:D7) →
1133.33
COUNTIF needs no third range, because counting rows does not need a column of numbers. AVERAGEIF has the same argument order as SUMIF. AVERAGEIFS has the same order as SUMIFS.
The argument order, in one table
| Function | Order |
|---|---|
| SUMIF | test range, condition, range to add |
| SUMIFS | range to add, test range, condition, … |
| COUNTIF | test range, condition |
| COUNTIFS | test range, condition, … |
| AVERAGEIF | test range, condition, range to average |
Questions people ask
Why does my SUMIF return 0?
Usually the condition is text that does not match. Check for a trailing space in the data with
=LEN(B2). Check that a number is a number with
=ISNUMBER(D2). A number stored as text passes no numeric test.
Can SUMIFS do OR instead of AND?
Not on its own. Its conditions are always joined with AND. Add two SUMIFS together, as in exercise 7, or use SUMPRODUCT for anything more complicated.
Are the ranges allowed to be different sizes?
No. Every range in one SUMIFS must be the same height. Different heights give
#VALUE! in SUMIFS, and a wrong answer in SUMIF, which is worse.
Should I use whole columns like B:B?
It is safe and it is slower, because Excel checks every row. On a small sheet it makes no
difference. The better habit is an Excel Table. Then write
Sales[Amount], which grows on its own and reads clearly.