(Note: This guide on how to count cells with text in Excel is suitable for all Excel versions including Office 365)
Excel deals with a variety of data of diverse data types. Excel can accept data in the form of numbers, text, characters, and operators. When storing large amounts of data, sorting and retrieving one particular type of data can be quite difficult.
In a spreadsheet consisting of different data types, you might sometimes have to pinpoint the cells with text and count them to perform any function or operation.
In this article, I will tell you how to count cells with text in Excel along with their use cases.
Watch our video on how to count cells with text in Excel How to Count Cells with Text in Excel – Workbook
When analyzing data in Excel, you may often need to count cells that contain text values. For example, you might want to count product IDs, client names, error codes or other text entries scattered throughout a dataset.
In this guide, I’ll show you a few easy ways to count cells with text in Excel using functions like COUNTIF COUNTIFS and SUMPRODUCT.
Why Count Text Cells in Excel?
Here are some common reasons you might need to count text cells in an Excel worksheet
- Counting textual product codes or SKUs in an inventory list
- Tallying the number of unique customer names in a contact database
- Checking the frequency of certain error values like “#N/A” or “#VALUE!”
- Totaling cells containing text remarks or comments
- Getting a count of qualitative responses in a survey
- Identifying rows where important ID columns are blank
Counting text values helps you analyze patterns, validate data, flag issues and obtain key dashboard metrics. Read on to learn how!
Count Cells with Any Text
The fastest way to count all cells containing text is with the COUNTIF function.
COUNTIF has a special wildcard syntax that lets you count cells with any text value.
Here is the formula structure:
=COUNTIF(range, “*”)
Where “*” counts any text, and “range” is the cells to evaluate.
For example, to count text cells in A2:A100, use:
=COUNTIF(A2:A100,”*”)
This will tally all cells in the range that have text entries, ignoring blanks and numeric cells.
Count Cells With Specific Text
To count only cells with a given text value, put the actual text string inside the quotes instead of the wildcard.
For example, to count “Error 405” codes, use:
=COUNTIF(A2:A100,”Error 405″)
This counts only exact matches of “Error 405”.
You can also use wildcards to do partial matching. For example:
=COUNTIF(A2:A100,”Error“)
This formula counts any cells containing the substring “Error” anywhere inside the text.
Count Text Cells With Multiple Criteria
To apply more complex criteria, use the COUNTIFS function to specify multiple conditions.
For example, to count text cells that are not blank or empty strings:
=COUNTIFS(A2:A100,”<>”,A2:A100,”<>”&””)
The first criteria counts text cells. The second criteria excludes empty strings.
You can add more range/criteria pairs to apply additional logic.
Count Text Lengths
To conditionally count text cells based on the length of text, use LEN combined with COUNTIF:
=COUNTIF(A2:A100,LEN(A2:A100)>10)
This counts only text cells where the length is greater than 10 characters.
Replace the 10 with any numerical length to filter.
Count Text Cells With Formulas
The preceding examples work on “hardcoded” text values entered directly into cells.
But you may also need to count text generated by formulas.
For this, use COUNTIF with ISFORMULA:
=COUNTIF(A2:A100,ISFORMULA(A2:A100))
This counts all formula cells that output text results.
You can combine it with other criteria to be more selective.
Count Text Cells With SUMPRODUCT
SUMPRODUCT is another good option for counted text cells. The benefit is it allows “array” processing of entire ranges.
The formula uses the ISTEXT function to test for text:
=SUMPRODUCT(–ISTEXT(A2:A100))
ISTEXT returns an array of TRUE/FALSE values, which get converted to 1/0 by the double unary (–).
SUMPRODUCT then sums the 1s, effectively counting the text cells.
Again, you can add additional range/criteria pairs inside SUMPRODUCT to apply more logic tests.
Create a Text Cells Count Summary
Once you’ve counted text cells, you can output a summary report on a dashboard worksheet.
For example:
| Metric | Count |
|---|---|
| Total Text Cells | =COUNTIF(DataRange,”*”) |
| Error 405 | =COUNTIF(DataRange,”Error 405″) |
| Text Length > 10 | =COUNTIF(DataRange,LEN(DataRange)>10) |
This lets you visualize the key text metrics at a glance.
Create a Text Count Pivot Table
Another good option is to create a pivot table and add the text count field to the values area.
You can instantly filter, sort and analyze the text metrics across your dataset.
The flexibility of pivots makes them ideal for investigating text patterns.
Format Text Count Cells
To make text count cells stand out, apply conditional formatting rules like:
- Bold/color text when above a threshold
- Data bars to visualize relative magnitudes
- Color scales to highlight outliers
This helps flag important metrics visually in the workbook.
Refresh Text Counts When Data Changes
Since text counts rely on worksheet functions, they update automatically when the source data changes.
But for pivot tables, you’ll need to refresh the pivot after adding new data to pick up text count changes.
Setup automatic pivot refreshes to avoid missing updates.
Limitations When Counting Text
A few limitations to note when counting text in Excel:
- Functions like COUNTIF only work on text in the same workbook – not external files. You need Power Query for external data.
- Pivot tables can’t directly count text cells. You need a helper column with formulas.
- Large data sets may require Power Pivot to avoid slow formula recalculations.
- Functions don’t understand linguistic patterns – the text match is literal.
But overall, Excel gives you solid tools for tallying and reporting on text cells in your workbooks.
The COUNTIF, COUNTIFS, SUMPRODUCT, and ISTEXT functions, combined with PivotTables and formatting, provide diverse options for counting text occurrences in your datasets.
Count All the Text Values
To count all the text values in the given Excel sheet, you can use the COUNTIF function along with a wildcard character. This function with a wildcard counts all the text values in a given range.
To count the cells with text in Excel, choose a destination cell and enter the formula =COUNTIF(range,criteria). Here, the range denotes the array of cells within which you want the function to act. The criteria variable denotes the condition to satisfy when counting the values.
Consider the below given example. To find the cells with text values in a given range, enter the formula =COUNTIF(A3:A10,”*”). The function COUNTIF acts on the cell range A3 to A10 and finds the text values. The * represents the wildcard element. The * symbol specifies anything other than numbers to be counted, including blank spaces and special characters. However, this method does not count logical values.
The cells with text within a given range are found to be 7. If you count the values manually, you will notice that the cells with text values are 5. But, the cells A11 and A12 also contain characters like space ( ) and apostrophe (‘) which do not show up in the cell.
Also Read:
Count Cells with a Particular Text Value
With the help of wildcards and the COUNTIF function, you can also count the cells with any specific text values in Excel.
Using this method, you’ll learn how to count specific words in Excel. To count the cells with the specific value, you can just enter the text to count within quotations when you pass the criteria parameter.
Let me show you an example. Suppose you want to count the occurrences of the word “two” in a range of cells. You can just enter the formula in the =COUNTIF(A3:A12,”two”) to count the occurrences of the word “two” in the given range of cells A3 to A12.
Wildcards can be used to count cells with a specific text value.
Imagine you want to count the number of cells that contain the text starting with any particular letter or character. Say, you want to count the number of cells that starts with the letter “t”, then you can use the “*” wildcard.
Consider the example, in the cell range A3 to A12 you want to count the number of cells which start with the letter “t”. Then, enter the function =COUNTIF(A3:A12,“t*”) in the destination cell.
The resultant value is 2. This denotes that when you want to find the cells which start with the letter “t”, you can enter the value “t*” in the criteria parameter. As a result, the COUNTIF function counts the cells which start with the letter “t” irrespective of the number of characters that follow them.
Another case to use the wildcard is to count any one particular value. For example, if you want to find the occurrences of a three-letter word that have the starting letter “t” and ends with “o”, you can use the “?” wildcard. As the “*” wildcard replaces any number of characters, the “?” wildcard only replaces one character.
In this case, the count function only counts the cells with three characters which start with “t” and end with “o” and counts them.
Suggested Reads:
- Wildcards can take the place of characters and are of three types: *, ?, and ~. Use the * wildcard when you want to replace more than one character, use the ? wildcard to replace exactly one character, and ~ to replace and search for the exact character. Wildcards are not case-sensitive. Additionally, wildcards only work on text and not on numbers.
- Sometimes when a cell appears blank, it does not necessarily mean that the cell is empty. Characters like space ( ), apostrophe (‘), and double quotation marks(””) also make the cell look empty. But technically, they are considered text.
- When looking for cells with text manually, you can easily distinguish the text from the number in Excel. Numbers are aligned to the right of the cell, whereas text is aligned to the left of the cell.
How to Count Cells With Text In Excel Using Formula
How to count the number of cells that contain text?
The COUNTIF and SUMPRODUCT functions in Excel can both be used to count the number of cells that meet particular criteria. In this case, we’ll use them to count the number of cells that contain text only. To count the number of cells that contain text, first select the range of cells that you want to count. In this case, that range is B3:D15.
How to count cells in Excel?
Select the cell where you want the result to appear. COUNT: To count cells that contain numbers. COUNTBLANK: To count cells that are blank. COUNTIF: To count cells that meets a specified criteria. Tip: To enter more than one criterion, use the COUNTIFS function instead. Select the range of cells that you want, and then press RETURN.
How to count cells in a range that contain text values?
To count cells in a range that contain text values, you can use the COUNTIF function and the asterisk (*) wildcard. In the example shown, the formula in cell H5 is: =COUNTIF (data,”*”) where data is the named range B5:B15. The result is 4 because four cells in the range B5:B15 contain text values.
How to count blank cells in Excel?
Excel contains several functions to help you count the number of cells in a range that are blank or contain certain types of data. Select the cell where you want the result to appear. COUNT: To count cells that contain numbers. COUNTBLANK: To count cells that are blank. COUNTIF: To count cells that meets a specified criteria.
