## Count cells with text

*Article updated on March 26, 2018*

The following formula in cell D3 counts cells with values stored as text.

In other words, cells containing nothing, errors, boolean values, and numbers are not counted.

Numbers stored as text are counted, as well as cells containing a space a character or more.

#### Explaining formula

*Step 1 - Identify values stored as text*

The ISTEXT function returns TRUE or FALSE depending on if a cell has a value stored as text.

ISTEXT(B3:B14) returns {TRUE; FALSE; FALSE ... }

**Step 2 - Convert boolean values to numbers**

To count boolean values (TRUE, FALSE) we need to convert them into numbers. TRUE = 1 and FALSE = 0 (zero).

ISTEXT(B3:B14)*1 becomes {TRUE; FALSE; FALSE ... }*1

and returns {1; 0; 0; ... }

**Step 3 - Count numbers**

The SUMPRODUCT function has a great advantage over the SUM function, in most cases, you don't need to enter the formula as an array formula if you are working with arrays.

SUMPRODUCT(ISTEXT(B3:B14)*1)

becomes

SUMPRODUCT({1;0;0;1;0;0;1;1;0;1;0;0})

and returns 5 in cell D3. 1+0+0+1+0+0+1+1+0+1+0+0 = 5.

### Count cells with text excluding cells containing a space character

Cell B4 contains a space character, the **array formula** below does not count cell containing a space character.

B3:B14<>" " makes sure that cells containing a space character are not counted.

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

The formula below lets you count text values returned from an Excel function.

The ISTEXT function handles empty values from an Excel function as a text value. To avoid counting those non-values I simply use the smaller than and larger than signs <> meaning not equal to.

Cell B5 contains a number stored as text, to exclude that number use the following formula:

### Download Excel *.xlsx file

Lookup with multiple criteria and return multiple search results

Question: How do I search a list containing First name column and a last name column? I want to search […]Search for a text string and return multiple adjacent values

This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]Filter unique distinct values if value contains specific string [Formula and Advanced Filter]

Table of contents Filter unique distinct values using a "contain" condition Filter unique distinct values using a "contain" condition (Advanced […]Create a unique distinct list where a corresponding column has text cell values

Question: I want a unique list to be created from a column where an adjacent column has text cell value? […]Extract unique distinct values sorted based on sum of adjacent values

Table of Contents Filtering unique distinct text values and sort them based on sum of adjacent values Filtering unique distinct […]### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

## Share this article