Counting cells with text in Excel sounds like a simple task, but it can quickly become tricky depending on your data set. Whether you need to count cells that contain specific text, filter out empty cells, or tally text values while skipping numeric values, Excel offers multiple ways to handle text matching.

In this guide, we will cover everything from basic COUNTIF formulas to advanced array formulas and developer-focused automation tools.

Method 1: The COUNTIF Function (The Standard Way)

The standard way to count cells with text in Excel is using the COUNTIF function. The COUNTIF function counts the number of cells within a range that meet specific criteria.

Formula Structure

To count all cells containing any text string in a range, use the following formula with a wildcard character:

=COUNTIF(A2:A11, "*")

=COUNTIF(A2:A11, "*")

The asterisk (*) acts as a wildcard character representing any sequence of characters.

Step-by-Step

1. Select the cell where you want the total count to appear.

2. Enter =COUNTIF(A2:A11, "*") into the formula bar, replacing A2:A11 with your target range.

   

3. Hit enter to get your result.

   

Method 2: Counting Cells with Specific Text or Partial Match

When analyzing spreadsheet data, you often need to count cells that contain a specific word, phrase, or sub-string.

Counting Specific Text (Exact Match)

To count cells that contain an exact text string, enclose the phrase in double quotation marks:

=COUNTIF(A1:A10, "apple")

=COUNTIF(A1:A10, "apple")

This COUNTIF formula checks the cell reference range and counts every instance where the text equals "apple".

Partial Match with Wildcards

If you need a partial match, for instance, counting cells that contain "apple" anywhere inside a longer text string, wrap the term in asterisks:

=COUNTIF(A1:A10, "*apple*")

=COUNTIF(A1:A10, "*apple*")

• Starts with specific text: =COUNTIF(A1:A10, "apple*")
• Ends with specific text: =COUNTIF(A1:A10, "*apple")
• Single character wildcard: Use the question mark (?) to match any single character. For example, =COUNTIF(A1:A10, "a?ple") matches "apple" or "ample".

Method 3: Counting Text Values While Excluding Empty Cells and Numbers

A common issue in Excel is distinguishing between text values, numeric values, true and false values, and blank cells.

How to Count Non-Empty Cells

If your goal is simply to count non-empty cells regardless of data type, use the COUNTA function:

=COUNTA(A1:A10)

=COUNTA(A1:A10)

However, COUNTA counts all cells with data, including numeric values, errors, and boolean true and false values.

Counting ONLY Text Cells (Excluding Numbers & Blanks)

To count cells with text in Excel while ignoring numeric values, formulas that return numbers, and empty cells, combine SUMPRODUCT and ISTEXT:

=SUMPRODUCT(--ISTEXT(A1:A10))

=SUMPRODUCT(--ISTEXT(A1:A10))

Method 4: Case Sensitive Text Search

Because COUNTIF is case insensitive, searching for upper and lower case distinctions requires a different approach using the EXACT function and SUMPRODUCT function.

=SUMPRODUCT(--EXACT("Apple", A1:A10))

=SUMPRODUCT(--EXACT("Apple", A1:A10))

This formula compares the search string "Apple" against each cell in the range. It returns TRUE only when the case matches exactly, converting false values to 0 and true values to 1.

How to Count the Number of Cells with Unique Text in Excel

To count the number of cells that contain unique text strings while ignoring duplicate entries and blank cells, use this array formula:

=SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10, A1:A10&""))

=SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10, A1:A10&""))

How this formula counts:

1. A1:A10<>"" filters out empty cells and blank strings.
2. COUNTIF(A1:A10, A1:A10&"") evaluates how many times each item appears in the range.
3. SUMPRODUCT sums the reciprocal values, yielding the exact count of unique text entries.

Quick Comparison of Text Counting Methods

Troubleshooting Common Errors

• Cells with invisible spaces: Cells containing hidden space characters or empty strings ("") from formulas may be counted as text by COUNTIF. Fix this by cleaning your data range using TRIM() or CLEAN().
• Incorrect cell reference: Double-check your named range or absolute cell references ($A$1:$A$10) when copying formulas across a row or column to prevent shifting criteria.
• Unexpected numeric skips: Remember that standard wildcards like "*" ignore numeric values completely. If your text string is actually stored as a number, use SUMPRODUCT instead.

Automating Spreadsheet Operations with IronXL

For developers building enterprise applications, handling spreadsheet calculations manually isn't an option. IronXL is a robust .NET library designed to manage Excel files programmatically in C#, VB.NET, and F# without requiring Microsoft Excel installation.

Whether you need to count cells based on specific criteria, extract text from massive data sets, or validate user inputs in a spreadsheet, IronXL simplifies document processing in just a few lines of code.

Counting Text Cells Programmatically in C#

Using IronXL to load an existing worksheet, iterate through a target range, and count cells that contain text:

using System;
using IronXL;

// Load an existing Excel workbook
WorkBook workbook = WorkBook.Load("DataReport.xlsx");
WorkSheet worksheet = workbook.DefaultWorkSheet;

int textCellCount = 0;

// Iterate through a designated range of cells
foreach (var cell in worksheet["A1:A100"])
{
    // Check if the cell value contains text and is not blank
    if (cell.IsText && !string.IsNullOrEmpty(cell.StringValue))
    {
        textCellCount++;
    }
}

Console.WriteLine($"Total number of cells with text: {textCellCount}");

using System;
using IronXL;

// Load an existing Excel workbook
WorkBook workbook = WorkBook.Load("DataReport.xlsx");
WorkSheet worksheet = workbook.DefaultWorkSheet;

int textCellCount = 0;

// Iterate through a designated range of cells
foreach (var cell in worksheet["A1:A100"])
{
    // Check if the cell value contains text and is not blank
    if (cell.IsText && !string.IsNullOrEmpty(cell.StringValue))
    {
        textCellCount++;
    }
}

Console.WriteLine($"Total number of cells with text: {textCellCount}");

IronXL Output

Summary

Knowing how to count cells with text in Excel efficiently saves hours of manual data checking. For quick interactive analysis, use the COUNTIF function with wildcards like * and ?. When working with complex criteria, case sensitivity, or filtering out numbers, use SUMPRODUCT alongside ISTEXT or EXACT.

If your business needs to scale spreadsheet workflows, perform automated reporting, or process data server-side, check out IronXL to handle Excel automation in code. Ready to try it in your own projects? Start your fully functional 30-day free trial of IronXL to experience fast, code-driven spreadsheet manipulation.