How to count cells with a specific text in excel

August 1, 2022
1.2K Views
0

A free Office suite fully compatible with Microsoft Office

Free Download

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.

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.


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.

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.