In Excel, you can count the number of cells that contain text using the COUNTIF function. This can be useful when you want to quickly identify how many cells in a range contain text values, such as names, addresses, or descriptions.
Guide How to Count Cells with Text in Excel
Select the cell where you want the count to appear.
Type the following formula into the cell.
=COUNTIF(range,"*")Replacing “range” with the range of cells you want to count.
Press Enter to calculate the result.
What’s Happening with the Function?
The COUNTIF function counts the number of cells in a range that meet a specified condition. In this case, we’re using the function to count cells that contain any text.
The asterisk (*) is a wildcard character that matches any number of characters, including zero. By using the asterisk in the criteria argument, we’re telling Excel to count any cell that contains text, regardless of what that text is.
Things to Keep in Mind
The COUNTIF function is case-insensitive, so it will count cells with text in any combination of uppercase and lowercase letters.
If you want to count cells that contain a specific text value, you can replace the asterisk in the formula with the text you want to count.
For example
=COUNTIF(range,"apple")This would count the number of cells in the range that contain the word “apple”.
If you want to count cells that contain text along with other values, such as numbers or dates, you can use a combination of the COUNTIF function and other functions like SUMPRODUCT or SUMIFS.
Count Cells with Text in Excel Example
Suppose you have a range of data in column A, and you want to count how many cells contain text. You can use the following formula:
=COUNTIF(A1:A8,"*")
This formula counts the number of cells in column A that contain any text.
Here’s a breakdown of the formula:
- “COUNTIF” is the function we’re using to count the number of cells that meet a single criteria.
- “A1:A8” refers to the range of cells we want to count. In this case, we want to count all the cells in column A.
- “*” is the criteria we’re using. The asterisk * is a wildcard that means “match any text”. So we’re asking Excel to count all cells that contain any text.
Count Cells that Contain Specific Text
You can modify this formula to count cells that contain specific text by changing the criteria in the formula. For example, to count cells that contain the text “apple”, you can use the following formula:
=COUNTIF(A1:A8,"*apple*")
This formula counts the number of cells in column A that contain the text “apple”. The * wildcard is used both before and after the word “apple” to match any text that contains “apple”.
Count Cells that Contain Specific Capitalized Text
The COUNTIF function in Excel is not case sensitive by default, which means that it treats uppercase and lowercase letters as equivalent.
However, you can use the EXACT function to count cells that contain text that is exactly the same as a specified string, including the capitalization.
Here’s an example:
Suppose you have a range of data in column A that contains names, some of which are capitalized and others that are not. You want to count how many cells contain the exact string “John” with the capital “J”.
You can use the following formula:
=COUNTIF(A:A,EXACT("John",A:A))This formula counts the number of cells in column A that contain the exact string “John” with the capital “J”.
Here’s a breakdown of the formula:
- “COUNTIF” is the function we’re using to count the number of cells that meet a single criteria.
- “A1:A8” refers to the range of cells we want to count. In this case, we want to count all the cells in column A.
- “EXACT” (“John”,A1:A8) is the criteria we’re using. The EXACT function compares the string “John” (with a capital “J”) to each cell in the range A:A, and returns TRUE for cells that match the exact string. The COUNTIF function then counts the number of cells that match the criteria (i.e., where EXACT returns TRUE).
Note that this formula will only count cells that contain the exact string “John” with the capital “J”.