Count cells that contain text

Sample data. Items in A, counts in B, notes in C, the formula in E2.

The formula

Click E2 and type:

=COUNTIF(C2:C7,"*")

E2 shows 3: three items have a note. Empty cells are skipped.

• C2:C7 is the range to count in.
• "*" is a wildcard that matches any text at all.

Text, numbers or anything

COUNTA counts every cell that is not empty, text or number:

=COUNTA(B2:B7)

That gives 4 on the Count column. COUNT counts only numbers, so =COUNT(B2:B7) also gives 4, and =COUNTBLANK(B2:B7) counts the empty cells: 2 (Ink and Labels). When a column mixes text and numbers and you want the text only, ISTEXT never counts a number:

=SUMPRODUCT(--ISTEXT(B2:B7))

That gives 0 on the Count column and 3 on the Note column. The -- turns each TRUE into 1 and each FALSE into 0, and SUMPRODUCT adds them up.

Cells that contain a word

Put the word between two asterisks:

=COUNTIF(C2:C7,"*reorder*")

That gives 2: "Reorder" and "Reorder soon". Capitals do not matter. Without the asterisks, =COUNTIF(C2:C7,"Reorder") gives 1, only the cell that is exactly Reorder. To use a word typed in another cell, say G2:

=COUNTIF(C2:C7,"*"&G2&"*")

Starts with or ends with

One asterisk at the end means "starts with":

=COUNTIF(A2:A7,"P*")

That gives 2, Pens and Paper. One at the start means "ends with": =COUNTIF(C2:C7,"*soon") gives 1. A question mark stands for exactly one character, and a tilde before * or ? counts the symbol itself: =COUNTIF(C2:C7,"*~?*") counts notes with a question mark in them.

Text that is not a certain word

This looks right but counts the empty cells too:

=COUNTIF(C2:C7,"<>Reorder")

It gives 5, because a blank cell is also "not Reorder". Add the asterisk as a second condition to count only notes that are something else:

=COUNTIFS(C2:C7,"*",C2:C7,"<>Reorder")

That gives 2: Ask supplier and Reorder soon.

The count is higher than the notes you see

A cell with only a space in it looks empty but holds text, so COUNTIF and COUNTA count it. Check a cell that looks empty:

=LEN(C2)

0 means truly empty, 1 means one space. Select those cells and press Delete to clear them. COUNTIF, COUNTIFS, COUNTA, COUNT, COUNTBLANK and LEN work in every current version of Excel and in Google Sheets.

Templates with the math already done

Rather skip the formulas? These templates come with every formula built and checked, plus a filled-in sample.