Free Practice WorkbookWhen working with data in Excel, there are times when I need to count how many cells contain specific text. Excel has a few handy functions that make this task simple. These functions help save time and make large datasets easier to manage and analyze accurately. In this article, I will show you how to count if cell contains text in Excel.
Key Takeaways:
- Use COUNTIF to count cells with specific text.
- COUNTA counts all non-empty cells in a range.
- COUNTIFS helps apply multiple conditions together.
- Wildcards can find partial text matches easily.
- Functions like TRIM and ISTEXT help avoid errors.
Table of Contents
How to Count Cell Containing Text
COUNTA Function
The COUNTA function is useful when you quickly want to identify how many cells contain any value without checking each cell manually.
STEP 1: Select an empty cell.
STEP 2: Input the COUNTA formula.
STEP 3: Enter the range of the cells.
STEP 4: Press Enter.
Here, 11 rows were selected in the range, but the output is 10 because one of the cells was empty. This function will count all cells with text within the column range A1:A11.
COUNTIF Function
The =COUNTIF() function is my go-to tool for counting cells with specific text.
For instance, I can find out the number of employees under the “Operations” department by using this formula
The COUNTIF function can also count numbers and dates, and even apply logical statements for a more dynamic data analysis.
Advanced Tricks
COUNTIFS FunctionThe
COUNTIFS function allows me to apply several criteria across different ranges. It is useful because I can analyze data with nuance, such as counting the instances of a particular text occurring on certain dates or within specific categories.
Using Wildcards
Wildcards are especially helpful when the text values are inconsistent or when only part of a word is known. The asterisk * and question mark ? become powerful allies when counting partial matches or single, uncertain characters, respectively. It helps me count cells that meet specific criteria, even if I’m only searching for partial matches.
Let’s say I have a list of product names in column A, and I want to count how many of these product names contain the word “Apple.”
This would return 5 because five product names in the list contain the word “Apple.”
Common Pitfalls
If the data contains leading or trailing spaces, I can use the TRIM function to remove those spaces.
If I am not sure whether the cell contains text or numbers, I can use the ISTEXT function.
Checking the formatting of cells before applying formulas can also help avoid incorrect counting results and calculation errors.
FAQs
How to count non-blank text cells in Excel?
To count non-blank text cells in Excel, you can use the COUNTIF function with an asterisk (*). For example, =COUNTIF(A1:A10, "*") counts all cells in the range A1 to A10 that contain any text.
Which function can be used to count non-empty cells?
The COUNTA function counts all non-empty cells. It counts text, numbers, and dates.
How to count cells with partial text matches?
You can use wildcards with the COUNTIF function to count cells with partial text matches.
How to count cells that meet multiple criteria?
You can use the COUNTIFS function to count cells with text that meets multiple criteria.
How to check if a cell in Excel contains certain text?
You check if a cell in Excel contains certain text using the COUNTIF function with a wildcard.
=COUNTIF(A1, "*text*")
John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship MyExcelOnline Academy Online Course.
30 Days - Full Access 
