## Count cells with text

*Article last 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

Count cells equal to any value in a list

The formula in cell F9 counts the number of cells in column B (Values1) that are equal to any of the […]

Match two criteria and return multiple records

Question: I have a table of 3 columns (Security name, date, price) and I have to find the price of […]

Find latest date based on a condition

Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download […]

Table of Contents Find closest value Find closest values Find closest values and return adjacent values Find closest value with […]

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 […]

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 […]

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 […]

Filter unique distinct values, sorted and blanks removed

Table of Contents Filter unique distinct values, sorted and blanks removed Filter duplicate values, sorted and blanks removed EEK asks: […]

Count unique distinct text values in a range

Array formula in D14: =SUM(IF(FREQUENCY(COUNTIF(tbl, "<"&IF(ISTEXT(tbl), tbl, "")), COUNTIF(tbl, "<"&IF(ISTEXT(tbl), tbl, "")))>0, 1, 0)) + CTRL + SHIFT + ENTER […]

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? […]

How to create a list of random unique numbers

Question: How do I create a random list of unique numbers from say 1 to 10, without using VBA and […]

Merge two columns with possible blank cells

Question: This article is terrific. Thanks so much for posting this solution! I do have one question: Let's say my […]

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 […]

Formula for matching a date within a date range

Table of contents Match a date when a date range is entered in a single cell Match a date when […]

Identify overlapping date ranges

The formula in cell F6 returns TRUE if the date range on the same row overlaps another date range in […]

Overview This article describes how to create a random playlist of four teams total. Column A contains four teams. Each […]

### Leave a Reply

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form