How many orders were for notebooks? How many students passed? How many cells are blank? Every one of these is a counting question with a single condition, and COUNTIF answers it in one formula. It is the simplest of the counting functions, and the one you will use most often.
What COUNTIF does
COUNTIF looks through a range and counts the cells that match your rule. The rule can be exact text, a number, a comparison like “>100”, a wildcard pattern like “*book*”, or a blank check. It counts cells, not values — so a value that appears three times is counted three times.
The syntax
=COUNTIF(range, criteria)
- range — the cells to look through, for example the Product column.
- criteria — the rule, for example “Notebook”, “>100”, “*book*”, or “” for blanks.
Step-by-step example
Count the notebook orders and the orders over $100 from this sales list.
- Click the cell where the answer should go.
- Type
=COUNTIF(B2:B9,"Notebook")and press Enter — the count of notebook rows appears. - To count by amount, type
=COUNTIF(D2:D9,">100"). The comparison operator goes inside the quotes. - To count with a value from another cell, join it with
&:=COUNTIF(B2:B9,F2)where F2 holds the product name. - To count blank cells, use
=COUNTIF(A2:A9,""). To count non-blank cells, use=COUNTA(A2:A9)instead.
Worked example
| Region | Product | Amount |
|---|---|---|
| East | Notebook | $120 |
| East | Pen set | $85 |
| West | Notebook | $200 |
| East | Notebook | $95 |
=COUNTIF(B2:B5,"Notebook") returns 3, and =COUNTIF(D2:D5,">100") returns 2. The same range can answer many questions — only the criteria changes.
Common errors
0 when the value is clearly there. Almost always extra spaces or a case of numbers stored as text. "120" as text is not the number 120, and "Notebook " with a trailing space is not "Notebook". TRIM the data first.
Wildcards count too much. The pattern "*book*" matches Notebook, storybook, and handbook. If you want an exact match, drop the asterisks — "Notebook" alone matches only that word.
Dates do not match. A date written as text (“2026-01-05”) is not a date to COUNTIF. Either store real dates or compare against the text exactly as it appears, quotes and all.
Counting blanks gives a strange number. COUNTIF with "" counts truly empty cells, but a formula that returns "" looks blank and is counted too. Use =COUNTBLANK(range) when you want both kinds of blank.
Tip
COUNTIF is case-insensitive: “notebook” matches “Notebook”. If you need a case-sensitive count, use SUMPRODUCT with EXACT — but for almost everyone, COUNTIF’s behaviour is what they want. And when one condition is not enough, COUNTIFS takes the same criteria and adds more pairs, so the step up from COUNTIF is a small one.
Counting blanks, numbers, and everything
Three functions cover the counting family: COUNT counts numbers only, COUNTA counts anything that is not empty (text, numbers, errors), and COUNTBLANK counts empty cells. So =COUNT(A2:A100) tells you how many values were entered, =COUNTA(A2:A100) tells you how many cells are filled at all, and =COUNTBLANK(A2:A100) tells you how many are still missing. Together with COUNTIF they answer almost every “how many” question without a filter, and they are the fastest way to check whether a column you imported is complete before you build anything on top of it.
Practice file
Download the COUNTIF practice file (.xlsx) — it contains a sales list and a summary sheet with COUNTIF answers already filled in, plus a blank sheet for you to try it yourself.
Google Sheets
Google Sheets supports COUNTIF with the same syntax, including wildcards. Sheets also has COUNTUNIQUE, which counts distinct values — handy when COUNTIF tells you how many rows there are but you want how many different ones.
Example prices and figures are illustrative, not market data.