COUNTIF and COUNTIFS practice, with answers
Eight counting exercises on one table, with answers: text, numbers, a comparison, dates, wildcards and two conditions at once, plus the quote marks rule.
COUNTIF counts the cells that pass one test. COUNTIFS counts the rows that pass several tests at once. Here are eight exercises on one table, with the answers.
Most counting mistakes are about quote marks. Exercises 3 and 4 show the rule. The table at the end puts it all in one place.
Type this table in A1 to E9. Type the dates as real dates, not as text. Write your answers in column G.
| Row | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Date | Product | Priority | Hours | Status |
| 2 | 2026-03-02 | Laptop 14 | 1 | 5 | Closed |
| 3 | 2026-03-05 | Printer | 3 | 30 | Closed |
| 4 | 2026-03-09 | Laptop 16 | 2 | 12 | Open |
| 5 | 2026-03-16 | Phone | 1 | 3 | Closed |
| 6 | 2026-03-23 | Printer | 2 | 26 | Open |
| 7 | 2026-04-01 | Laptop 14 | 3 | 48 | Closed |
| 8 | 2026-04-06 | Phone | 2 | 8 | Open |
| 9 | 2026-04-13 | Laptop 16 | 2 | 4 | Closed |
Exercise 1 — a word
How many tickets are still Open?
Answer: =COUNTIF(E2:E9,"Open") →
3
First where to look, then what to look for. There is no third argument, because a count adds nothing up.
The test ignores capitals, so "open" also gives 3. But a space
after the word in a cell stops the match, and you get no error.
Exercise 2 — a number
How many tickets have priority 1?
Answer: =COUNTIF(C2:C9,1) →
2
A plain number needs no quote marks. "1" in quotes gives 2 as
well. Both mean "equal to 1".
Exercise 3 — a comparison
How many tickets took more than 10 hours?
Answer: =COUNTIF(D2:D9,">10") →
4
This is the quote marks rule. The sign and the number go inside the quotes together. Type
=COUNTIF(D2:D9,>10) and Excel will not accept the formula.
Exercise 4 — a comparison with a cell
Type 24 in H2. Count the tickets that took more hours than the number in H2.
Answer: =COUNTIF(D2:D9,">"&H2) →
3
The sign is text, so it goes in quotes. The cell is not text, so it stays outside. The
& joins the two into one test.
">H2" gives 0. It compares the hours with the letters H2, not
with the number in H2. There is no error to warn you.
Exercise 5 — dates
How many tickets were opened in March 2026?
Answer:
=COUNTIFS(A2:A9,">="&DATE(2026,3,1),A2:A9,"<"&DATE(2026,4,1)) → 5
A month is two tests on the same column. The date is on or after 1 March. And it is before 1 April.
DATE takes a year, a month and a day. So the formula means the same day on every computer. A date typed as 03/04 can mean 3 April or 4 March.
Exercise 6 — part of a word
How many tickets are for a laptop, of any size?
Answer: =COUNTIF(B2:B9,"Laptop*") →
4
The star stands for any characters, or none. Without the star,
"Laptop" gives 0. No cell holds only that one word.
A question mark stands for exactly one character. So
"Laptop 1?" also gives 4. Both work on text, not on numbers.
Exercise 7 — two conditions at once
How many laptop tickets are Closed?
Answer:
=COUNTIFS(B2:B9,"Laptop*",E2:E9,"Closed") → 3
COUNTIFS takes pairs: a range, then its test. A row counts only when it passes every pair. Each
range must be the same height, or you get #VALUE!.
Exercise 8 — one or the other
How many tickets are for a printer or a phone?
Answer:
=COUNTIF(B2:B9,"Printer")+COUNTIF(B2:B9,"Phone") →
4
Two counts, added. =COUNTIFS(B2:B9,"Printer",B2:B9,"Phone") gives
0. It asks for a ticket that is a printer and a phone at the same time.
Exercise 5 used one column twice, and it worked. A date can be after one day and before another. A product cannot be two things. COUNTIFS only ever means "and".
The quote marks rule, in one table
Signs and words go inside the quotes. A cell or a function stays outside, joined with
&.
| What you test | Write it like this |
|---|---|
| A word |
"Open"
|
| A number |
1
|
| A comparison |
">10"
|
| A comparison with a cell |
">"&H2
|
| A cell on its own |
H2
|
| A date |
">="&DATE(2026,3,1)
|
| Part of a word |
"Laptop*"
|
| Everything except |
"<>Closed"
|
Questions people ask
What is the difference between COUNT, COUNTA and COUNTIF?
COUNT counts numbers only. COUNTA counts every cell that is not empty. COUNTIF counts the cells
that pass a test. On this table, =COUNT(B2:B9) gives 0, because
the products are text. =COUNTA(B2:B9) gives 8.
How do I count everything except one value?
Put <> in front of it, inside the quotes.
=COUNTIF(E2:E9,"<>Closed") gives 3. The sign means "not equal
to".
Why does my COUNTIF return 0?
Usually the test is text that does not match. Check a cell for extra spaces with
=LEN(B2). Then check the quote marks. A cell reference inside the
quotes, as in exercise 4, gives the wrong count.
Where can I practise this with checking?
In the COUNTIF and COUNTIFS lessons below. Each lesson is a real sheet you type into, and your answer is checked when you press Enter.