Count Numbers, Text, and Blanks Separately

Excel Formulas › Count

All versionsCOUNT

Three counting functions, three jobs: COUNT tallies numbers, COUNTA tallies non-blank cells, and COUNTBLANK tallies empties. Knowing which is which avoids classic miscounts.

Quick formula: count numeric cells in A2:A100:
=COUNT(A2:A100) // numbers only =COUNTA(A2:A100) // non-blank (any type) =COUNTBLANK(A2:A100) // empty cells
COUNT ignores text and blanks; COUNTA counts anything present; COUNTBLANK counts the gaps.

Functions used (tap for the full reference guide):

COUNTCOUNTA

The example

A mixed column tallied three ways.

AB
1FunctionResult
2COUNT (numbers)3
3COUNTA (non-blank)5
4COUNTBLANK1

The formula

Three functions for three questions:

=COUNT(A2:A100) // numbers =COUNTA(A2:A100) // non-blank =COUNTBLANK(A2:A100) // blanks

How it works

Each counts a different thing:

  1. COUNT counts cells holding numbers (and dates) — text and blanks are ignored.
  2. COUNTA counts every non-empty cell, regardless of type.
  3. COUNTBLANK counts the empty cells.
  4. COUNTA − COUNT = the count of text cells (and errors), a quick way to find non-numeric entries.

Find numbers stored as text: if COUNT is lower than you expect, some “numbers” are actually text. COUNTA − COUNT − COUNTBLANK isolates them — then convert with VALUE.

Try it: interactive demo

Live demo

One value per line (blank lines allowed).

Numbers · Non-blank · Blank

Variations

Text cells

Non-numeric entries:

=COUNTA(rng) - COUNT(rng)

Numbers stored as text

Isolate them:

=COUNTA(rng)-COUNT(rng)-COUNTBLANK(rng)

Count with a condition

Numbers over 0:

=COUNTIF(rng, ">0")

Pitfalls & errors

COUNT skips text. A column of “numbers” that’s really text counts as 0 with COUNT — a telltale sign of a type problem.

Empty string vs blank. A cell with ="" isn’t blank to COUNTBLANK in all versions — behavior can vary.

Errors count as non-blank. COUNTA includes error cells; COUNT does not.

Practice workbook

📊
Download the free Count Numbers, Text, and Blanks Separately practice workbook
A count-types sheet (COUNT/COUNTA/COUNTBLANK) with the text-cells and text-numbers variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

What's the difference between COUNT and COUNTA in Excel?
COUNT tallies only cells containing numbers; COUNTA tallies any non-empty cell (numbers, text, errors). COUNTBLANK counts empty cells.
How do I count text cells?
Subtract: =COUNTA(range) - COUNT(range) gives the count of non-numeric, non-blank cells.
How do I find numbers stored as text?
If COUNT is lower than expected, =COUNTA(range)-COUNT(range)-COUNTBLANK(range) isolates the text-numbers; convert them with VALUE.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Count cells with text · Count blank cells · Convert text to number

Function references: COUNT · COUNTA