Featured image
COUNTIF in Excel
COUNTIF counts how many cells in a range match one condition: =COUNTIF(range, criteria). Text and numbers match exactly, a comparison is written as text with the operator inside the quotes as ">10", and wildcards work, so "A*" counts everything beginning with A.
=COUNTIF(range, criteria)
COUNTIF is the quickest diagnostic tool in a spreadsheet. Before trusting a SUMIF, run a COUNTIF with the same criterion: if it reports zero rows, the problem is the criterion and not the arithmetic, and you have saved yourself an hour of staring at the sum.
It is also how duplicates get found. Counting how many times each value appears in its own column turns an invisible problem into a column of numbers, and anything above 1 is a row worth looking at.
The arguments
Square brackets in the line above mark an optional argument. Optional in the syntax and optional in practice are not always the same thing, and where they differ this page says so.
- The cells to look through. One range only; COUNTIFS is the version that takes more.
- What counts as a match. "North" or 12 matches exactly, ">10" is a comparison with the operator inside the quotes, ">"&C2 compares against a cell, and "A*" uses a wildcard.
Worked examples
Every example below is written against the same six-row table, and every result was produced by running the formula in this site’s spreadsheet engine rather than typed in by hand. The grid underneath holds that table, so you can try each one.
Count one category
=COUNTIF(B2:B7, "North")Returns 2
Two rows hold North. The match ignores capitals, so "north" returns the same 2, which is worth knowing before building a report that treats them as different categories.
Count the rows above a threshold
=COUNTIF(C2:C7, ">10")Returns 3
The criterion is a comparison, so the operator and the number go inside the quotes together. Three reps sold more than ten units. Fay's blank cell is not counted, because a blank is not greater than ten.
Count with a wildcard
=COUNTIF(A2:A7, "*a*")Returns 4
An asterisk stands for any number of characters, so this counts every name containing an a anywhere: Ada, Blake, Dara and Fay. A question mark stands for exactly one character instead.
Compare against a cell rather than a typed number
=COUNTIF(C2:C7, ">"&C2)Returns 2
The ampersand joins the operator to whatever C2 holds, which is 12. Two reps beat Ada. Building the criterion this way is what lets a threshold live in a cell somebody can change without editing formulas.
Try it here
Try it here
A real spreadsheet, not a picture of one. Press an example to write it into A9, then edit it and watch the answer change.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rep | Region | Units | Revenue | Joined | Grade |
| 2 | Ada | North | 12 | 4800 | 15 Jan 2024 | A |
| 3 | Blake | South | 7 | 2100 | 2 Mar 2024 | C |
| 4 | Chen | North | 19 | 7600 | 20 Nov 2023 | A |
| 5 | Dara | East | 4 | 1200 | 8 Jun 2024 | B |
| 6 | Eli | South | 15 | 5250 | 30 Sep 2023 | B |
| 7 | Fay | East | 900 | 11 Feb 2024 | C | |
| 8 | ||||||
| 9 | ||||||
| 10 |
Type straight into a cell, press F2 to edit one, and drag the fill handle to copy a formula down. Nothing here is saved.
Finding duplicates with COUNTIF
Point a COUNTIF at a column and test it against its own first cell, with the range locked and the cell left relative. Copied down, each row then reports how many times its own value appears in the whole column.
=COUNTIF($A$2:$A$7, A2)Returns 1. 1 means this value is unique. Anything higher is a duplicate, and the number tells you how many copies there are.
- 1Put the formula in a spare column beside the first data row.
- 2Lock the range with dollar signs and leave the single cell reference relative, which is the entire trick.
- 3Copy it down the column.
- 4Filter or sort on the new column for anything greater than 1.
Writing criteria
- Exact text: "North". Capitals ignored.
- A number: 12, with or without quotes.
- A comparison: ">10", "<=100", "<>North" for not equal. The operator lives inside the quotes.
- Against a cell: ">"&C2, joined with an ampersand.
- Wildcards: "A*" begins with A, "*a*" contains an a, "??at" is any two characters followed by at.
Errors and what causes them
Each formula below really returns the error beside it in the grid on this page. Paste one into the sandbox above if you want to see it happen, which is a faster way to learn an error value than reading about it.
=COUNTIF(B2:B7, "North")/COUNTIF(B2:B7, "West")WhyA share-of-total calculation where the denominator counted nothing. There is no West region, so the division has zero underneath it.
FixThe error is doing its job: it says the category you divided by is empty. Check the spelling of the criterion before reaching for IFERROR, because an empty category and a misspelled one look identical from here.
=COUNTIF(B2:B7, "North") + "x"WhyCOUNTIF itself almost never errors; it returns 0 when nothing matches. An error here comes from what surrounds it.
FixEvaluate the COUNTIF on its own in a spare cell to confirm it returns a number, then look at the formula around it.
What goes wrong
These are the mistakes that come up again and again with COUNTIF. Each one is reasonable. That is why people make it.
Reading a result of 0 as a broken formula.
WhyZero looks like failure, and COUNTIF gives no error to distinguish nothing matched from something is wrong.
InsteadZero is an answer: no cell matched. Check the criterion against the real contents of the column, allowing for trailing spaces and numbers stored as text.
Writing the operator outside the quotes.
WhyIt is how a comparison looks in every other part of a formula.
Instead">10" as one piece of text, or ">"&C2 when comparing against a cell.
Leaving the quotes off a text criterion.
WhyThe word is just a word, and the formula still runs.
InsteadQuote it: "North". Excel reports the bare word as #NAME?; the grid here matches nothing and returns a calm zero, which is the same error with no warning attached.
Expecting COUNTIF to respect capitalisation.
WhyNothing in the function suggests otherwise.
InsteadIt does not. "north", "North" and "NORTH" all match each other. SUMPRODUCT with EXACT is the usual workaround.
Forgetting to lock the range when copying a duplicate check down.
WhyThe first row gives a believable answer.
Instead$A$2:$A$7 for the range, A2 relative for the value. Without the dollar signs, each row counts within a window that has slid down with it, and the result is meaningless.
Common questions
How do I count cells greater than a number?
Put the whole comparison in quotes: =COUNTIF(C2:C7, ">10"). To compare against a cell, join with an ampersand: =COUNTIF(C2:C7, ">"&E1).
How do I find duplicates with COUNTIF?
Put =COUNTIF($A$2:$A$7, A2) in a spare column and copy it down. Every row then shows how many times its own value appears; anything above 1 is a duplicate.
What is the difference between COUNTIF and COUNTIFS?
COUNTIF takes one range and one criterion. COUNTIFS takes any number of pairs, all of which must hold on the same row. Both take their arguments in the same order, unlike SUMIF and SUMIFS.
Why does COUNTIF return 0?
Nothing matched. The usual causes are a trailing space in the data, a number stored as text, or a criterion that differs from the data in some way the eye skips over. Try a wildcard such as "*North*" to see whether a near match exists.
Does COUNTIF count blank cells?
Not for a numeric comparison: a blank is neither greater than nor less than a number. COUNTBLANK counts empties directly, and is the right function for that question.