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.
Download the Practice Workbook
<< Go Back to With Text | Count Cells | Formula List | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
The SUMPRODUCT(–ISTEXT(range)) worked for me. My range included TEXT and zeros. Thank you for being there!
Dear Carl,
You are most welcome.
Regards
ExcelDemy
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
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.
If AutoFill is not working please check out this article: Excel Autofill is Not Working
If Auto Calculations is not working check out this article: Excel Formulas Not Calculating Automatically
If this solutions doesn’t work for you, please provide more details about what you were able to do before and what is not working now. You can attach dummy image of your dataset. This will help me give you the most accurate advice.
Regards
ExcelDemy