# How to Count If a Cell Contains Any Text in Excel (4 Methods)

We’ll use a sample monthly chocolate sales report to demonstrate how you can count cells that contain text.

## Method 1 – Using the COUNTIF Function to Count If a Cell Contains Any Text in Excel

Steps:

• Select Cell D17 to store the count result.
• Insert the following function:
`=COUNTIF(C5:C14, "*")`
• Hit Enter.

Formula Breakdown

General Structure: =COUNTIF(range,criteria)

• In the range section, we input C5:C14, which is the range of the column in which we’ve run the COUNTIF function.
• The criteria is an asterisk (*), which is a wildcard that matches any number of text characters.

## Method 2 – Adding up If a Cell Contains Any Text in Excel Using the SUMPRODUCT Function

Steps:

• Select the cell D17 to store the count result.
• Insert the following formula:
`=SUMPRODUCT(--ISTEXT(C5:C14))`
• Hit Enter.

Formula Breakdown

General Structure: =SUMPRODUCT(–ISTEXT(range))

• In the range section, we input C5:C14, which is the range of the column in which we’ve run the SUMPRODUCT function.
• The double negative operator before ISTEXT converts Boolean values into numerical ones.

## Method 3 – Using the COUNTIFS Function to Count If a Cell Contains Texts with Specific Criteria

Steps:

• Select cell D17 for the result.
• Insert the following formula:
`=COUNTIFS(C5:C14,"*",C5:C14,"<> ")`
• Hit Enter.

Formula Breakdown

General Structure: =COUNTIFS(range,”*”,range,”<> “)

• In the range section, we input C5:C14, which is the range of the column in which we’ve run the COUNTIFS function.
• The first condition is an asterisk (zero or more other characters), which checks cells that contain characters at all.
• The second condition is “not equals space” so we’re ignoring the cells that contain only a space.

## Method 4 – Adding up If a Cell Contains Partially Matched Text in Excel

Steps:

• Cell C17 contains the match term “Almond.”
• Insert the following formula in D17:
`=COUNTIF(C5:C14,"*"&C17&"*")`
• Hit Enter.

## Things to Remember

• The COUNTIF function counts a single space as a text value.
• Be careful while inserting the range and criteria for each of the functions.

<< Go Back to With Text | Count Cells | Formula List | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Mrinmoy Roy

Mrinmoy Roy, a dedicated professional with a BSc in Electronics and Communication Engineering from Khulna University of Engineering & Technology, Bangladesh, brings over two years of expertise to the ExcelDemy project. As a prolific contributor, he has authored around 180 articles, showcasing his deep knowledge and passion for Microsoft Excel, Data Analysis, and VBA. His unwavering commitment to continuous learning, combined with versatile skills, renders him well-suited for roles in data management and spreadsheet solutions. He has interest... Read Full Bio

1. The SUMPRODUCT(–ISTEXT(range)) worked for me. My range included TEXT and zeros. Thank you for being there!

2. Hi there! I use the ctrl: to enter dates in a cell. I’m trying to add all cells in this column that have this (this will be the only entry in the column) but it continues to show 0 as the function value. I’ve tried all of these steps.

• Dear Kelley Sauer,
Please use this formula below to count sales having date.
`=COUNTIF(E5:E14, "<>")`

It will count all sales with date. Using this formula, you will not get zero anymore. Moreover I have also used Ctrl+: to insert date.
With Regards,
Joyanta Mitra

3. I normally just scroll down a list of items and the number of cells with values (words or letters) automatically adds as I go. this no longer works.

• Hello Joe Bardswich,

Your statement is not clear whether you referring to AutoFill or Auto Calculations. I am providing solution for both of them.