How to count cells with specific text?

The data users enter various types, such as numeric values, numbers, symbols, or a combination. To calculate the data, the user requires only specific text from the cell. Is it easy to partition or split the data for a small data set but for a large one? For extensive data sets, partitioning the data takes more time and is complex. To rectify this problem, Excel provides various formulas to highlight or count the specific cell.

Why count specific text in Excel?

There are various reasons to count specific text in Excel. Some of the reasons are listed as follows,

1. Checking the frequency of certain words or phrases: If you're working with a large dataset in Excel, you may need to know how often certain words or phrases appear. For example, if you're analyzing customer feedback, you may want to know how often customers used words like "excellent" or "poor" to describe their experience.
2. Finding errors or inconsistencies: Counting specific text can help you identify errors or inconsistencies in your data. For example, if you're working with a list of product names, count how many times a particular word appears to ensure no duplicates or misspellings.
3. Tracking progress: If you're using Excel to track progress on a project or task, count how often a certain status or milestone has been reached. For example, if you're managing a team, you may want to know how often a team member has completed a particular task.

Counting specific text in Excel can help you gain insights into your data and make more informed decisions based on that information.

Methods to count specific text in a cell

The various methods to count specific text in Excel are explained as follows,

1. Counting text string in Excel

To count specific text in Excel, you can use the COUNTIF function. Here are the steps to follow:

1. Select the cell where you want to display the count result.
2. Type the following formula into the formula bar:

Replace "range" with the range of cells that contain the text you want to count. For example, if you're going to count the number of times the word "Google" appears in cells A1 through A5, your range would be "A1:A5".

Replace "criteria" with the text you want to count. For example, if you're going to count the number of times the word "Google" appears, your criteria would be "Google."

1. Press Enter to calculate the count.

For example, if you want to count the number of times the word "Google" appears in cells A1 through A5, your formula would look like this:

This will return the count of cells in the range A1:A5 that contain the text "Google."

From the worksheet, in each cell, one text string is present. But how to count the specified text among the multiple text string present in the cell?

The formula will be modified as follows,

In cell B1, the formula displays the word count of Google as 1. To get the result for the remaining data, drag the fill handle toward cell B5.

Formula description

The formula works by subtracting the original range's length from the range's size with the criteria replaced by an empty string. This gives the total number of characters in the requirements removed from the field. Dividing this by the criteria length indicates the number of times the standards appear in the range.

2. Calculate the specific text using SUMPRODUCT and ISNUMBER function

You can also calculate a particular value using Excel's SUMPRODUCT and ISNUMBER functions. Here are the steps to follow:

Step 1: Select the cell where you want to display the sum result.

Step 2: Type the following formula into the formula bar as

=SUMPRODUCT (--(ISNUMBER (FIND ("text", range)))).

This case-sensitive formula is used to find the string of the selected cell using three different functions in the recipe.

In the worksheet to find the word AA, the formula is modified as =SUMPRODUCT (--(ISNUMBER (FIND (C1, A1:A10)))). The procedure returns the result as 3. As this formula is case-sensitive, it returns the exact search result.

Formula description

The SUMPRODUCT function in Excel is used to multiply corresponding elements within arrays and then sum the results. It takes one or more arrays as arguments and returns the sum of their products.

The ISNUMBER function is used to check whether a given value is a numeric value or not. It returns TRUE if the value is numeric and FALSE otherwise.

The FIND function is used to search for a specific substring within a larger string and returns the starting position of the substring if it is found.

Case Sensitive formula to count specific text

The SUMPRODUCT and EXACT functions find the case-sensitive formula to count specific text.

The SUMPRODUCT function in Excel can also count specific text within a range of cells. To count cells that contain a particular string of text, you can use the SUMPRODUCT function with the -- operator to convert the logical values returned by the EXACT function to 1 or 0 and then sum the results.

Here's the syntax for using SUMPRODUCT and EXACT to count cells that contain a specific text string

Where, "text_to_count" is the text string you want to count

range is the range of cells you want to search within

The EXACT function compares two text strings and returns TRUE if identical. FALSE otherwise. In this case, we compare the text in each range cell to the text string we want to count. The -- operator converts the logical values returned by EXACT to 1 or 0, which SUMPRODUCT can then sum to get the total count of cells that contain the specified text string.

Here's an example usage of SUMPRODUCT and EXACT to count cells that contain the text string "America" in the range A1:A10

This formula will return the total count of cells that contain the text string "America" in the range A1:A10.

Here the SUMPRODUCT and EXACT return the count of the word America as one, which is the exact match.

How do you count cells beginning with the specified text and containing the selective text?

Cells that begin with selected text

To calculate the cells that begin with the specified text, the formula used is,

=COUNTIF (cell range,"*")

For example, to count the number of words starting with "FI" in the selected range, the formula is modified as,

=COUNTIF (A1:A10,"FI*")

Cells that contain specified text in any position

To count cells that contain the selective text in any position, the formula used is as follows,

=COUNTIF (cell range,"**")

For example, to count the number of words "AT" present in any position in the selected range, the formula used is,

=COUNTIF (A1:A10,"*AT*")

One can use cell references to make the formula more dynamic.

Count the cell that starts and ends with Specific text

To count the number of text strings that begin and end with a specified text in Excel, you can use the COUNTIFS function.

Assuming your list of text strings is in column A1:A5, and the specified text you want to check for is "ABC," you can use the following formula:

=COUNTIFS (A1:A5, "abc*abc")

This formula counts the number of cells in column A1:A5 that starts with "ABC" and ends with "ABC."

The number of text strings starting and ending with ABC is two cells. The COUNTIF function returns the result as 2 in the selected cell.

Here's how the formula works:

COUNTIFS: This function counts the number of cells that meet multiple criteria.

A1:A5: This is the range of cells you want to count.

"abc*abc": This is the criteria you want to check for. The asterisk * acts as a wildcard that matches any number of characters between "ABC" at the beginning and end of the string.

Find and Replace Method

You can also use the Find and Replace method in Excel to count specific text within a range of cells. Here's how you can do it:

Step 1: Select the range of cells you want to search within.

Step 2: Press Ctrl+H to open the Find and Replace dialog box.

Step 3: In the "Find what" field, enter the text string you want to count.

Step 4: Leave the "Replace with" field blank.

Step 5: Click on the "Find All" button.

Excel will display a list of all the cells that contain the text string you searched for.

Count the number of cells in the list to get the total count of cells with the specific text string.

Note: This method is only helpful for small ranges of cells, as Excel will display a list of cells that match the search criteria, which can be cumbersome to count for more extensive ranges.

Also, to count cells containing a partial text string, you can use wildcards in the "Find what" field. For example, if you want to count cells that contain the word "AT" anywhere within the text, you can use the wildcard "*" symbol like this:

*AT*

This will search for any text string containing the word "AT" anywhere.

Here the three cells are found with the words "AT" in the selected range of cells.

Summary

In conclusion, counting cells with specific text in Excel is a common task in data analysis and can be accomplished using the COUNTIFS function. This function allows you to count cells that meet multiple criteria, including cells that start and end with a specific text string. Using wildcards, such as the asterisk *, you can match any number of characters between the starting and ending text strings. The COUNTIFS function is flexible and can be adjusted to fit different data ranges and specific criteria.