COUNTIF function
COUNTIF counts the cells that match a single condition, such as how many orders are over 100 or how many names start with A. It is one of the most used summary functions.
Example
Read it as a sentence: count the cells in C2:C7 that say Smoking.
How COUNTIF works
COUNTIF answers how many. How many orders are over 100? How many names start with A? How many times does this customer appear in the list?
It takes two things: where to look, and what a cell must match to count. That match can be a word, a number, a wildcard pattern, a cell, or an operator and a number together in quotes.
In the example above, the range is the Category column of the Items sheet. Excel checks all six cells against Smoking and counts three matches, from the briar pipe in C3 to the clay pipe in C6. The answer is 3.
Upper and lower case don't matter, so smoking finds Smoking too. For two or more conditions at once, use COUNTIFS.
Syntax
COUNTIF(range, criteria)
- Where to count
range(required) - Cells to examine.
- What a cell must match
criteria(required) - Condition such as 10, ">5" or "A*".
Returns: How many cells in the range meet the condition.
More examples
Each picture shows only the columns that formula reads.
-
Count the big values
How many items are worth 5 or more?
Excel shows 2E1Unit Value2$3.503$4.254$2.005$10.006$1.757$6.00ItemsPut the comparison in quotes. Two unit values qualify: the cigar and the commonplace book.
-
Compare with a cell
How many people are older than Holmes?
Excel shows 7E1Age2413434585486467448309301055113112321327146015141615PeopleThe > sign stays inside quotes and the cell joins it with &, giving ">"&People!E2. E2 holds Holmes's age, 41, and seven people are older.
-
Count cells that contain a word
How many items have pipe in the name?
Excel shows 2B1Item2Boxer cartridge3Briar pipe4Candle5Cigar6Clay pipe7Commonplace bookItemsAn asterisk on each side means pipe can sit anywhere in the text. The briar pipe and the clay pipe make 2.
-
Count empty and filled cells
How many people have no home on file?
Excel shows 2D1Home2L013L014L015L036L027L028L029L101011L1312L1413L1514L111516L01PeopleA condition of "" counts the empty cells: Moriarty and Wiggins, so 2. "<>" counts the cells that aren't empty, the other 13.
-
=COUNTIF(People!D2:D16,"<>")13
-
-
Flag a value that appears more than once
Does Lestrade's role appear more than once?
Excel shows TRUEC1Role2Detective3Doctor4Landlady5Civil servant6Inspector7Inspector8Inspector9Singer10Professor11Client12Client13Client14Client15Irregular16PagePeopleCount how often the role in C6 appears in the whole Role column, then test whether that count is above 1. Inspector appears three times, so the answer is TRUE. A FALSE would mean the role is unique.
-
Count between two numbers
How many items are worth from 2 to 6?
Excel shows 4E1Unit Value2$3.503$4.254$2.005$10.006$1.757$6.00ItemsCOUNTIF takes one condition, so count everything at 2 or more and take away everything over 6. That leaves 4. COUNTIFS can do the same with two conditions in one function.
Watch out for
Conditions longer than 255 characters give #VALUE!
A condition of 255 characters works: it finds no match here and returns 0. One character more and COUNTIF returns #VALUE!. Long text, such as a full address or a pasted sentence, can run into this limit.
-
=COUNTIF(Items!B2:B7,REPT("a",256))#VALUE! -
=COUNTIF(Items!B2:B7,REPT("a",255))0
A trailing space finds nothing
Smoking with a space after it is different text from Smoking, so the count is 0. When a count looks too low, check the condition and the cell it comes from for extra spaces.
-
=COUNTIF(Items!C2:C7,"Smoking ")0
"12" in quotes counts the number 12
COUNTIF turns a condition that looks like a number into a number, so "12" still counts the boxer cartridges' quantity of 12. A lookup doesn't do this: VLOOKUP with "12" in quotes ends in #N/A.
-
=COUNTIF(Items!D2:D7,"12")1 -
=VLOOKUP("12",Items!D2:E7,2,FALSE)#N/A
The range has to be cells on a sheet
COUNTIF counts cells, not a list typed into the formula. Give it {1,2,3} in braces and Excel refuses to enter the formula at all.
-
=COUNTIF({1,2,3},">1")Excel will not accept this formula
Either-or needs SUM around a list
Give COUNTIF two conditions in braces and it returns two counts, one for Tools and one for Smoking. SUM adds them into one answer, 4.
-
=SUM(COUNTIF(Items!C2:C7,{"Tools","Smoking"}))4
An asterisk on its own counts text, not numbers
* matches any text, so in the Unit Value column it finds only the header and returns 1. To count every filled cell, numbers included, use "<>".
-
=COUNTIF(Items!E1:E7,"*")1 -
=COUNTIF(People!D2:D16,"<>")13
Questions people ask
How do you use COUNTIF in Excel?
=COUNTIF(range, condition). Put comparisons in quotes, so ">=5" counts the values of 5 or more.
-
=COUNTIF(Items!E2:E7,">=5")2
How do I use COUNTIF with 2 criteria?
For conditions on two different columns, use COUNTIFS. For either of two values in the same column, add two counts: SUM(COUNTIF(range,{"Tools","Smoking"})) returns 4.
-
=SUM(COUNTIF(Items!C2:C7,{"Tools","Smoking"}))4
How can I count only cells with text in Excel?
Use "*" as the condition. It counts text and skips numbers, so all 15 names in the Name column are counted.
-
=COUNTIF(People!B2:B16,"*")15
Is COUNTIF case-sensitive?
No. A condition of smoking in lower case counts the same three Smoking cells.
-
=COUNTIF(Items!C2:C7,"smoking")3
Can COUNTIF count dates?
Yes. Join the operator to a DATE: two items were acquired on or after January 1, 2025.
-
=COUNTIF(Items!H2:H7,">="&DATE(2025,1,1))2
Where it works
- Excel
- COUNTIF is in every version of Excel from 2007 on.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Statistical functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .