How to Count Cells with A Specific Text in Excel

Have you ever wondered how to count the number of cells that contain a specific text in Excel? Or maybe there is a certain number you want to achieve with this or a particular condition for some cells. This can be useful when you want to analyze the time you spent during different activities, where you have to know the general statistics of your working hours. For example, maybe you want to count all the products that have a specific word in their product name. Or maybe you want to count all products from a certain manufacturer.

In this article, we will show you the many ways to count cells in Excel with a specific text. This article is helpful when you need to generate a report of the countable numbers containing a particular text from a list of data you have.

Tips that are provided in this article are compatible with versions 2010/2013/2016.

How To Count Cells With A Specific Text In Excel: Find and Replace method

The Find and Replace method is the easiest way to count cells with a specific text. It's simple, but it's not the most accurate method.

Find and Replace is a valuable tool in Excel. When you use Find and Replace, you can search for a specific word or phrase and replace it with something else. In this case, we'll use Find and Replace to count the number of times a word appears in a range of cells.

Here are the steps to follow:

1. Press CTRL+ H inside your workbook.

2. A window will open asking to find a specific word.

3. Search your desired word here and see the result.

Now, as you can see from above, it has highlighted the word as well as the location of that word.

Pros:

• Fast: The Find and Replace method is generally fast and efficient, especially for smaller datasets or when searching for a single specific text.

• Easy: The process is straightforward and doesn't require writing complex formulas or functions. It's a user-friendly method accessible to Excel users of all levels.

Cons:

• Time-consuming for large datasets: When dealing with large datasets, the Find and Replace method may take more time as it searches through each cell individually.

• Limited to exact text matches: The method only counts cells with an exact text match. If you need to count cells with variations or partial matches, you may need to use other methods like formulas or functions.

How To Count Cells With A Specific Text In Excel: COUNTIF Function

The following way to find a cell with a specific text is using the COUNTIF function. The COUNTIF function counts cells based on the value of one or more criteria. The syntax is =COUNTIF(range, criteria). You can enter any number of criteria separated by commas in the formula. The value must be either exact or an approximate match.

The syntax of this, in our case, is

=COUNTIF(A1:A7,” Apple”)

The results say it all.

With this function, you can easily count cells based on a wide range of conditions, such as if it's less than or greater than a number, contains one or more characters, and many more.

Pros:

• Easy implementation: The COUNTIF function in Excel is user-friendly and simple to use, requiring only a range and a specific text as criteria.

• Quick results: The function provides a fast way to count cells with specific text, allowing for efficient data analysis.

• Versatility: The COUNTIF function is not limited to counting cells with specific text; it can also be used to count cells that meet various criteria, including numbers and expressions.

Cons:

• Exact match requirement: The COUNTIF function requires an exact match for the specified text, meaning it may not capture variations or partial matches.

• Limited to one criterion: The function can only handle one criterion at a time, so counting cells based on multiple conditions may require alternative approaches such as using the COUNTIFS function.

How To Count Cells With A Specific Text In Excel: Search Function

The search function is a built-in Excel function that allows us to search through a range of cells for a specific word or phrase. It's straightforward to use and can save you tons of time if you're trying to find information on a list or spreadsheet.

To use the search function, simply go up to the top of your worksheet where all of your data is located (the A column) and type in:

=SEARCH(range, text )

Now you know how to Count Cells with A Specific Text In Excel by using the Search Function.

Pros:

• Flexibility in matching: The Search function provides greater flexibility in matching text, as it can identify partial matches, regardless of case sensitivity. This allows for a broader range of counting possibilities.

• Multiple criteria: Unlike the COUNTIF function, the Search function enables users to search for and count cells based on multiple criteria, providing a more versatile approach to data analysis.

• Handling expressions and patterns: With the Search function, users can count cells based on specific expressions or patterns, making it a powerful tool for advanced data analysis tasks.

Cons:

• Complexity: The Search function may be more complex to implement compared to the simple COUNTIF function, as it requires users to use an array formula and understand the intricacies of text searching.

• Slower for large datasets: The Search function can be slower when dealing with large datasets, especially if multiple criteria are involved, potentially impacting performance.

Although Microsoft Office and WPS Office can also operate these functions, but I still recommend that you use WPS Office, it is a free office software, has a user-friendly interface and has all the features of Microsoft Office, come to click on the following download card, into the WPS Office journey.

Free Office Download

   

• Use Word, Excel, and PPT for FREE, No Ads.

• Edit PDF files with the powerful PDF toolkit.

• Microsoft-like interface. Easy to learn. 100% Compatibility.

• Boost your productivity with WPS's abundant free Word, Excel, PPT, and CV templates.

Free Download

100% secure

Algirdas Jasaitis

FAQs

1. How do I use Conditional Formatting to count cells with specific text?

To use Conditional Formatting in Excel to count cells with certain text, select the range of cells you want to count, go to the "Home" tab, click "Conditional Formatting," and choose "New Rule." Choose "Use a formula to figure out which cells to format" and type in a formula that uses the COUNTIF function, such as "=COUNTIF(A1:A10,"example")>0," to count how many times the word "example" appears in cells A1 to A10. Applying the formatting will count and highlight the cells that meet the condition once it has been defined.

2. How do I subtotal count cells with text in Excel?

To subtotal the number of text-containing cells in Excel, select the range of data and navigate to the "Data" tab. Click "Subtotal" in the "Outline" group, then select the column for grouping and "Count" as the function. Check the box next to the column that contains text values. Click "OK" to apply subtotaling, and the number of text-containing cells will be displayed in the designated column based on the selected grouping column.

3. How do I count occurrences of a specific text string in a column in Excel?

Use the COUNTIF function in Excel to count the occurrences of a specific text string in a column. Enter the formula "=COUNTIF(range, "text")" in a cell for the result. Replace "range" with the column range to search (for example, A1:A10) and "text" with the specific text string to count. The formula will count the number of times the text string appears in the specified column range and display the result in the cell of choice.

Conclusion

Did you learn how to count cells with a specific text in Excel? Excel You can follow WPS academy to learn more features of Word documents, Excel Spreadsheets, and PowerPoint slides.

You can also download WPS Office to edit word documents, Excel Spreadsheets, and PowerPoint free of cost. Download Now! And get an enjoyable working space.