C# Excel 程式庫

Explore IronXL C# .NET 的 Excel 試算表程式庫開始免費試用

無需 Interop 即可讀取 Excel 檔案

using IronXL;
using System;
using System.Linq;

// Supported for XLSX, XLS, XLSM, XLTX, CSV and TSV
WorkBook workBook = WorkBook.Load("sample.xlsx");

// Select worksheet at index 0
WorkSheet workSheet = workBook.WorkSheets[0];

// Get any existing worksheet
WorkSheet firstSheet = workBook.DefaultWorkSheet;

// Select a cell and return the converted value
int cellValue = workSheet["A2"].IntValue;

// Read from ranges of cells elegantly.
foreach (var cell in workSheet["A2:A10"])
{
    Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text);
}

// Calculate aggregate values such as Min, Max and Sum
decimal sum = workSheet["A2:A10"].Sum();

// Linq compatible
decimal max = workSheet["A2:A10"].Max(c => c.DecimalValue);

using IronXL;
using System;
using System.Linq;

// Supported for XLSX, XLS, XLSM, XLTX, CSV and TSV
WorkBook workBook = WorkBook.Load("sample.xlsx");

// Select worksheet at index 0
WorkSheet workSheet = workBook.WorkSheets[0];

// Get any existing worksheet
WorkSheet firstSheet = workBook.DefaultWorkSheet;

// Select a cell and return the converted value
int cellValue = workSheet["A2"].IntValue;

// Read from ranges of cells elegantly.
foreach (var cell in workSheet["A2:A10"])
{
    Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text);
}

// Calculate aggregate values such as Min, Max and Sum
decimal sum = workSheet["A2:A10"].Sum();

// Linq compatible
decimal max = workSheet["A2:A10"].Max(c => c.DecimalValue);

PM >Install-Package IronXL.Excel

Install-Package IronXL.Excel

EXCEL 工具

How to Count Cells with Text in Excel: A Step-by-Step Guide (.NET 10, C#)

Curtis Chau

Updated: 2026年8月16日

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, "*")

Text

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

Step-by-Step

Select the cell where you want the total count to appear.
Enter =COUNTIF(A2:A11, "*") into the formula bar, replacing A2:A11 with your target range.
Hit enter to get your result.

請注意: The COUNTIF function is case insensitive by default, meaning "APPLE", "Apple", and "apple" will match as the exact same text values.

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")

Text

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

COUNTIF Exact match output

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*")

Text

COUNTIF partial match output

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)

Text

Counting all non-empty cells 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))

Text

Counting only text cells

提示: The double unary operator -- converts TRUE and FALSE logical outputs from ISTEXT into 1s and 0s, allowing SUMPRODUCT to sum the total number of text entries accurately.

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))

Text

Case sensitive text search 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&""))

Text

Counting the number of unique cells

How this formula counts:

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

Quick Comparison of Text Counting Methods

Goal / Criteria	Formula Example	Handles Wildcards?	Case Sensitive?
All text cells	=COUNTIF(A1:A10, "*")	Yes	No
Specific text match	=COUNTIF(A1:A10, "apple")	Yes	No
Partial match	=COUNTIF(A1:A10, "apple")	Yes	No
Only text (ignore numbers)	=SUMPRODUCT(--ISTEXT(A1:A10))	No	N/A
Exact case match	=SUMPRODUCT(--EXACT("Apple", A1:A10))	No	Yes

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}");

Text

IronXL Output

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.