COUNTIF explained, with practice exercises
COUNTIF counts how many cells in a range meet a condition. For example how many orders came from Hamburg, or how many invoices are over 1,000.
In short: =COUNTIF(range,criteria). Excel checks every cell in the range and counts the ones the criteria matches. Text and comparisons go in quotes.
Try it now
The sheet lists orders with their city and revenue. Count the orders from Hamburg. Click the yellow cell and write the formula. Selecting a range while you type inserts its reference.
Task In E2, use COUNTIF to count how many orders came from Hamburg.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order no. | City | Revenue $ | Orders from Hamburg | |
| 2 | B-2201 | Hamburg | 340 | ||
| 3 | B-2202 | Berlin | 1280 | ||
| 4 | B-2203 | Hamburg | 760 | ||
| 5 | B-2204 | Cologne | 95 | ||
| 6 | B-2205 | Hamburg | 2150 | ||
| 7 | B-2206 | Berlin | 480 | ||
| 8 | B-2207 | Munich | 610 |
The COUNTIF formula
| Argument | Meaning | In the example |
|---|---|---|
| range | The cells to check and count | B2:B8 (the cities) |
| criteria | The condition a cell must meet: text, a number, a comparison or a cell reference | "Hamburg" |
So the full formula is =COUNTIF(B2:B8,"Hamburg"), which returns 3. The match ignores case, so "hamburg" would count as well.
Writing criteria
Most COUNTIF problems come down to how the criteria is written. These are the forms you will use most:
| Criteria | Counts cells that … |
|---|---|
"Berlin" | contain exactly Berlin |
"<>Berlin" | do not contain Berlin (empty cells included) |
">5" | hold a number greater than 5 |
">=1000" | hold a number of 1000 or more |
"B*" | start with B. The asterisk stands for any number of characters. |
"*Ltd*" | contain Ltd anywhere |
"Sm?th" | match Smith or Smyth. The question mark stands for exactly one character. |
"" | are empty |
To take the threshold from a cell, join the operator and the reference with an ampersand: =COUNTIF(C2:C8,">"&E2). The operator stays inside the quotes, the reference stays outside. For a plain equality check the reference alone is enough: =COUNTIF(B2:B8,E2).
One more: invoices of 1,000 or more
This time you count amounts that reach a limit. Decide whether an invoice for exactly 1,000 belongs in the count.
Task In E2, count how many invoices are for at least 1,000.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Invoice | Customer | Amount $ | Invoices of 1,000 or more | |
| 2 | R-1041 | Baker Ltd | 850 | ||
| 3 | R-1042 | Hill & Sons | 1200 | ||
| 4 | R-1043 | Northwind Inc | 1000 | ||
| 5 | R-1044 | Carter Ltd | 430 | ||
| 6 | R-1045 | Hill & Sons | 2300 | ||
| 7 | R-1046 | Fisher Co | 990 | ||
| 8 | R-1047 | Baker Ltd | 1500 |
More than one condition: COUNTIFS
COUNTIF takes a single condition. COUNTIFS takes pairs of range and criteria and counts a row only when every pair matches:
=COUNTIFS(B2:B8,"Hamburg",C2:C8,">=500") counts Hamburg orders worth at least 500. Use the same range twice to count values between two limits: =COUNTIFS(C2:C8,">=500",C2:C8,"<=1000"). To add up values under several conditions instead of counting them, use SUMIFS. Date ranges, criteria from cells and exercises for all of this are on our COUNTIFS page.
Common COUNTIF mistakes
Criteria without quotes
Comparisons and text need quotes. Excel refuses =COUNTIF(C2:C8,>=1000) as soon as you press Enter, and =COUNTIF(B2:B8,Hamburg) returns #NAME? because Hamburg is read as a name. Only plain numbers and cell references work without quotes.
The reference inside the quotes
">E2" compares against the text E2, not the value in that cell. Write ">"&E2 instead.
Extra spaces in the data
A cell holding "Hamburg " with a trailing space does not match "Hamburg". The space is invisible and often comes from exported data. Clean the column with =TRIM(B2) in a helper column, or use a wildcard such as "Hamburg*".
Ranges of different sizes in COUNTIFS
Every range in COUNTIFS must have the same number of rows and columns. =COUNTIFS(B2:B8,"Hamburg",C2:C9,">500") returns #VALUE! because the second range is one row longer.
> when you meant >=
">1000" leaves out 1000 itself. If the task says "at least" or "or more", use ">=1000".
COUNTIF in German Excel
In a German Excel the function is called ZÄHLENWENN and arguments are separated by semicolons: =ZÄHLENWENN(B2:B8;"Hamburg"). COUNTIFS becomes ZÄHLENWENNS. The criteria themselves are written the same way. Our exercises accept both spellings.
Which Excel versions have COUNTIF?
COUNTIF and COUNTIFS are available in all current versions: Microsoft 365, Excel 2016, 2019, 2021 and 2024, Excel for Mac and Excel for the web.
FAQ
How do I count cells that are not empty?
Use "<>" as the criteria: =COUNTIF(B2:B8,"<>"). COUNTA does the same job without a criteria.
How do I count cells that contain one value OR another?
Count each case and add them: =COUNTIF(B2:B8,"Berlin")+COUNTIF(B2:B8,"Hamburg"). COUNTIFS always joins its conditions with AND.
How do I search for a real asterisk or question mark?
Put a tilde in front of it. "~*" matches an asterisk character instead of treating it as a wildcard.
What is the difference between COUNTIF and COUNT?
COUNT counts every cell that holds a number, with no condition. COUNTIF counts only the cells your criteria matches, whether they hold numbers or text.