To count cells that contain text in a range, you can use the COUNTX Excel function or the COUNTIF function with a wildcard.
How to Count cells that contain text
- Type =COUNTX(B3:B9,1) in cell D3
- Press Enter
- The formula will count cells that contain text
Generic Formula with COUNTX function
Before we look at the example, here is the generic formula.
Syntax:
=COUNTX(range, data_type)Arguments:
The function uses two required arguments, the range containing various data types (text, number, dates, boolean values, errors, or formulas. The second argument is data_type, which controls the type of data you want to count.
The COUNTX function is a part of our user-defined function library, DataFX. The add-in contains a huge amount of useful functions in various categories.
Example
We have a data set in the range B3:B9. The goal is to count cells that contain text values. Configure the COUNTX function arguments by setting the second argument to 1.
Formula:
=COUNTX(B3:B9,1)
Workaround with COUNTIF and ISTEXT functions
In the example, you want to count only these cells that contain text values. Therefore, the generic formula combines the COUNTIF function with the ISTEXT function.
Create a new column and apply the following formula in cell C3:
=ISTEXT(B3:B9)ISTEXT checks whether a value is a text and returns TRUE or FALSE. In the example, we want to count cells in the range B3:B9 that contain text values. We added various data types, text, numbers, dates, and formula errors. Copy the formula down.
Enter the following formula in cell D3:
=COUNTIF(C3:C9, TRUE) The formula uses the COUNTIF function and counts the number of cells in a range that match the criteria. In the example, we have three cells that contain text values.
Using FALSE as the second argument, count the non-blank cells in a range.
=COUNTIF(C3:C9, FALSE)