The COUNT function

Counts the cells that hold numbers (dates count too). Text, blanks and errors are not counted.

How to use COUNT

COUNT answers one question: how many numbers are in this range? It is like counting the price tags on a shelf while skipping the labels that only have words on them. Dates count too, because Excel stores a date as a number.

Use COUNT when you want to know how many amounts, scores or dates you have, for example how many invoices have an amount filled in or how many students handed in a score. =COUNT(A2:A6) counts the cells in A2:A6 that hold a number. You can add more ranges or plain numbers: =COUNT(A2:A6,10) counts the numbers in A2:A6 and the 10 on top.

Text, empty cells and errors are not counted. That is what you want when a column mixes numbers with notes, but it surprises people when a list of names gives 0.

To count every cell that is filled, text included, use COUNTA. To count the empty cells, use COUNTBLANK. To count only the cells that meet a condition, such as sales over 300, use COUNTIF: =COUNTIF(C2:C6,">300").

Syntax

COUNT(value1, [value2], ...)
value1
The first value, cell or range.
value2 (optional)
More values or ranges.

Examples

C1=COUNT(A2:A6)
ABC
1Units3
24
3n/a
47
5
62
C1=COUNT(A2:A6,10)
ABC
1Units4
24
3n/a
47
5
62

In plain words: Count the numbers in A2:A6 plus the number 10.

Common COUNT mistakes

  • COUNT gives 0 on a list of names

    COUNT counts numbers only, so names, emails and other text are skipped. Use =COUNTA(A2:A6) to count every filled cell.

  • COUNT is lower than the number of values I can see

    Some of the numbers are stored as text, often after a paste or import, so COUNT skips them. Compare with =COUNTA(A2:A6): if COUNTA is higher and the cells look like numbers, they are text. Convert them with =VALUE(A2) in a helper column.

  • COUNT counts zeros and dates I did not want

    A zero is a number and a date is a number, so both are counted. To count only values above zero, use =COUNTIF(A2:A6,">0").

Practice COUNT

Read the guide

Questions

What is the difference between COUNT and COUNTA?

COUNT counts only cells with numbers, dates included. COUNTA counts every cell that is not empty, including text, TRUE or FALSE, and errors.

How do I count cells with text in Excel?

COUNT skips text, so use COUNTIF with a wildcard: =COUNTIF(A2:A10,"*") counts the cells that hold text. To count a specific word, put it in the criteria, like =COUNTIF(A2:A10,"Shoes").

Does COUNT count dates?

Yes. Excel and Google Sheets store a date as a number, so a real date is counted. A date typed as text, such as one with a stray apostrophe, is not.

How do I count the empty cells in a range?

Use COUNTBLANK: =COUNTBLANK(A2:A10). It also counts cells that only hold an empty text result from a formula.

Related functions