Excel + Google Sheets · Deep guide
COUNTIF in Excel: count the rows that match
Sooner or later every spreadsheet needs a simple count: how many invoices are unpaid, how many customers come from one city, how many tasks are marked as done. Counting rows by eye is slow and unreliable, especially once the list grows past a screen.
COUNTIF counts the cells in a range that meet one condition. It works in Excel 2010 or later, in Excel 365 and in Google Sheets. This guide shows the syntax, a step by step example with real numbers and the mistakes that most often give a wrong count. See also: SUMIF guide and VLOOKUP guide.
How COUNTIF works
The function needs two pieces of information: the range to look at and the condition to meet. Write it like this:
=COUNTIF(B2:B6,"b")Here Excel looks in column B and counts how many cells contain the letter b. Text conditions are not case sensitive, and you can use an operator inside quotes for numbers. Works in Excel 2010 or later.
Step by step example
- Type the categories in column B, one per row.
- Click an empty cell, for example D2.
- Type
=COUNTIF(B2:B6,"b")and press Enter. - Compare the result with the table below.
| A | B | |
|---|---|---|
| 1 | Amount (A) | Category (B) |
| 2 | 10 | a |
| 3 | 20 | b |
| 4 | 30 | c |
| 5 | 40 | b |
| 6 | 50 | d |
The expected result is 2, because the category b appears twice. Replace the condition with an operator such as ">=30" on a numeric column to count only the values from 30 upwards.
Common errors
- The text has extra spaces, so b and b followed by a space are different. Clean the column with TRIM first.
- The condition has no quotes around the operator, for example >=30. Always write it inside quotes.
- The category does not exist and the result is 0. This is correct: no cell meets the condition.
- The numbers are stored as text, so numeric conditions miss them. Convert them to numbers.
When you need to count with two conditions at the same time, move on to COUNTIFS. Compare the result with a manual filter the first time, to be sure the condition is written correctly.
The same formula in other languages
- English COUNTIF
- Español CONTAR.SI
- Português CONT.SE
- Deutsch ZÄHLENWENN