How to / Counting

How to count cells with text in Excel

Use =COUNTIF(A2:A7, "*"). The asterisk stands for any text, so cells with numbers, dates or nothing in them are not counted.

Count the cells that hold text

ABC
1Entry
2Ana3
342
4
5Ben
62026-03-14
7n/a

In C2: =COUNTIF(A2:A7, "*") → 3

Ana, Ben and n/a are text. 42 is a number, the date is a number too, and A4 is empty.

How it works

  1. COUNTIF counts the cells in a range that meet one condition.
  2. In a condition, * is a wildcard that matches any run of characters. Only text can match a wildcard, so numbers and dates are left out.
  3. Empty cells are left out as well.

Variations

  1. Text that starts with a letter

    ABC
    1Entry
    2Ana1
    342
    4
    5Ben
    62026-03-14
    7n/a

    In C2: =COUNTIF(A2:A7, "A*") → 1

    Put the letters in front of the asterisk. "*a" would count text that ends in a.

  2. Every cell that is not empty

    ABC
    1Entry
    2Ana5
    342
    4
    5Ben
    62026-03-14
    7n/a

    In C2: =COUNTA(A2:A7) → 5

    COUNTA counts text, numbers and dates alike.

  3. Cells with numbers only

    ABC
    1Entry
    2Ana2
    342
    4
    5Ben
    62026-03-14
    7n/a

    In C2: =COUNT(A2:A7) → 2

    The date counts: Excel stores dates as numbers.

  4. Empty cells

    ABC
    1Entry
    2Ana1
    342
    4
    5Ben
    62026-03-14
    7n/a

    In C2: =COUNTBLANK(A2:A7) → 1

Watch out for

Doesn't work on your data? Paste your formula into the formula checker with a few rows of your sheet. It points at the part that fails and says why.

Functions used

Practise it

Related