Practice free

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.

Practice this in the app

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.

  1. Count the big values

    How many items are worth 5 or more?

    Put the comparison in quotes. Two unit values qualify: the cigar and the commonplace book.

  2. Compare with a cell

    How many people are older than Holmes?

    The > sign stays inside quotes and the cell joins it with &, giving ">"&People!E2. E2 holds Holmes's age, 41, and seven people are older.

  3. Count cells that contain a word

    How many items have pipe in the name?

    An asterisk on each side means pipe can sit anywhere in the text. The briar pipe and the clay pipe make 2.

  4. Count empty and filled cells

    How many people have no home on file?

    A 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
  5. Flag a value that appears more than once

    Does Lestrade's role appear more than once?

    Count 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.

  6. Count between two numbers

    How many items are worth from 2 to 6?

    COUNTIF 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 .