How to Count Cells With Text in Excel: COUNTA vs COUNT Explained

You need to count how many cells in a range contain text entries. This is a common task for data cleaning and reporting. Excel offers several functions for counting, but they behave differently with text. This article explains the correct functions to use and the key differences between them.

Understanding Excel’s Counting Functions

Excel provides a family of functions that begin with COUNT. Their purpose is to tally cells based on specific criteria. The most basic functions are COUNT and COUNTA. While their names are similar, their logic for evaluating cell content is fundamentally different. Knowing which one to use prevents inaccurate totals in your worksheets.

A cell containing text is any cell where the content is not purely a number, date, time, or logical value. This includes letters, words, combinations of letters and numbers like “Item123”, and even an apostrophe preceding a number. Excel stores these as text strings. A cell with only a space character is also considered text, though it may appear empty.

What COUNTA Counts

The COUNTA function is designed to count all cells that are not empty. Its logic is simple: if a cell contains anything, it gets counted. This includes numbers, text, dates, times, TRUE/FALSE values, and error messages like #N/A or #DIV/0!. It will even count a cell containing a formula that returns an empty string (“”). For counting text specifically, COUNTA is useful but not precise, as it includes all other data types.

What COUNT Counts

The COUNT function has a much narrower focus. It counts only cells that contain numeric values. This includes actual numbers, dates and times, percentages, and currency. It completely ignores cells containing text, logical values, and error messages. If your goal is to count text entries, using the COUNT function will always return zero, which is a common source of confusion.

Steps to Count Cells Containing Text

To accurately count only the cells that contain text, you must use the COUNTIF or COUNTIFS function. These functions allow you to set a criterion, such as “any text”. The asterisk wildcard character represents any sequence of characters.

1. Select the cell for your result
   Click on the empty cell where you want the count of text cells to appear.
2. Start the COUNTIF formula
   Type an equals sign (=) followed by COUNTIF( to begin the formula.
3. Define the range to check
   Select the range of cells you want to analyze, or type its address like A1:A100. Type a comma after the range.
4. Set the text criteria
   Type the criteria “*” including the quotation marks. The asterisk means “any text”. The complete formula looks like =COUNTIF(A1:A100, “*”).
5. Complete the formula
   Press the Enter key. The cell will now display the number of cells in your selected range that contain text.

Using COUNTA for a Total Non-Empty Count

If you need a count of all non-blank cells regardless of content type, use COUNTA.

1. Select the result cell
   Click on the cell for your total count.
2. Enter the COUNTA formula
   Type =COUNTA( and then select your cell range.
3. Close and execute
   Type a closing parenthesis ) and press Enter. The result is the count of all cells in the range that are not empty.

Common Mistakes and Limitations

COUNT Returns Zero for a Range Full of Text

If you use =COUNT(A1:A10) on a range containing only text entries, the result will be 0. This is not an error. The COUNT function is working as designed by ignoring non-numeric data. The fix is to use COUNTIF with the “*” criteria or COUNTA if you want to count all content.

Cells with Numbers Stored as Text Are Not Counted by COUNT

A cell containing the characters ‘500 or formatted as text will be seen as text by Excel. The COUNT function will ignore it, but COUNTA and COUNTIF with “*” will count it. To convert these text-numbers to real numbers, use the Paste Special > Multiply operation or the VALUE function.

COUNTIF with “*” Counts Cells with Only a Space

A cell containing only a space character, often entered by pressing the spacebar, is considered text. The COUNTIF “*” criteria will count it. To exclude cells that appear empty but contain spaces, you may need to clean your data first using the TRIM function.

Formulas Returning Empty Strings Are Counted by COUNTA

A formula like =IF(A1=””,””,”Data”) returns an empty string (“”) when true. COUNTA treats this as a non-empty cell and counts it, which can inflate your total. COUNTIF with “*” will not count these cells, as the asterisk looks for visible characters.

COUNT vs COUNTA vs COUNTIF: Key Differences

Item	COUNT Function	COUNTA Function	COUNTIF with “*”
Primary Use	Count numeric cells	Count all non-empty cells	Count cells containing any text
Counts Text Entries	No	Yes	Yes
Counts Numbers/Dates	Yes	Yes	No
Counts Logical Values (TRUE/FALSE)	No	Yes	No
Counts Error Values (#N/A, #DIV/0)	No	Yes	No
Counts Formulas Returning “”	No	Yes	No

You can now accurately count text cells in any dataset using COUNTIF with the asterisk wildcard. Remember that COUNTA gives you a broader total of all filled cells. For advanced analysis, explore the COUNTIFS function to count text cells that also meet other conditions, such as counting names in a specific department.