While working with a big range of data it is often required to find the number of specific text. In this article, we will count cells that contain specific text. In Excel, counting cells that contain specific text is an important task. We will also count cells that contain any text.
Table of Contents
- Count Cells that Contain Text
- Count Cells That Contain Specific Text
- Count Cells that Contain Specific One Text with a Different Formula
- Count Cells with Multiple Substring
- Excel VBA to Count Cells that Contain Specific Text
- Download The Working File
- Related Articles
Count Cells that Contain Text
Before counting cells that contain specific text we should see now the process of counting cells which contain any text. Two formulas can be used for counting cells that contain text. Here we have a list which ranges from A1 to B12. A1 and B1 cells contain the headings. We will be calculating cells that contain text for the range A2: B12. Write down the below formulas in any blank cells of your worksheet to find the result.
After applying the above two formulas you will get to see the below result.
Count Cells That Contain Specific Text
To count cells that contain specific text we will be using the same formula with a different argument. Let`s say we want to find the number of cells that have apples in it. It can be done in two ways. One process is finding the cells which contain the text apple (both apple & pineapple) anywhere in a text. Another process is finding the number of cells that contain the text “Apple” only.
Count cells with Match Criterion
For counting cells with match criterion type the formula,
=COUNTIF(A2:A12,"*Apple*") anywhere in your worksheet. Here in this worksheet, we placed it in cell E3. You can also indicate cell instead of text for finding the same result. Look at the below picture to get a clear view of the formulas and the results.
The formulas count all the cell that has the text apple in the range A2: A12.
Matching Specifically One Text
To count cells that have only the text “apple” we can use the formula
=COUNTIF(A1:A10,"Apple"). In cell E3, of the below picture we used this formula and found out there are exactly two cells that contain the text apple only. You can either write the cell name or specify the cell name to find the result. Like, instead of writing “apple” in the formula you can also write only A1 as it contains the text Apple.
Let`s make this more dynamic by indicating the fruits name in a different cell. You can use the data validation. Here we make a list using the data validation in cell E7 so that we can insert any fruit to count them in the list. The formula is applied in cell E8.
After applying data validation you will get the below result.
Count Cells that Contain Specific One Text with a Different Formula
We can use a different formula to count cells that contain a specific text. The formula has 3 functions which are, SUMPRODUCT, ISNUMBER and FIND. You can use the formula
=SUMPRODUCT(--(ISNUMBER(FIND(A2,A2:A12)))) for this. The formulas along with the result are shown below.
Count Cells with Multiple Substring
Let`s say you want to calculate the number of cells which full fill more that one text criterion. In this example, we have a list where different items are listed along with its color and size. We want to count the item T-Shirt with color blue which is in the range of A2: A9. To do this write down the formula
=COUNTIFS(A2:A9,"*T-Shirt*", A2:A9,"*Blue*") or
=COUNTIFS(A2:A9,"*"&D2&"*", A2:A9,"*"&D3&"*") in cell D4. You will find the accurate result that you want.
Excel VBA to Count Cells that Contain Specific Text
We can use Excel VBA code for counting cells of specific text. To perform this, click on the Visual Basic under the Developer tab and insert the below code in there.
Dim iVal As Integer
iVal = Application.WorksheetFunction.CountIf(Range("A2:A12"), "Apple")
Here you can edit the range and specific text to perform in your worksheet. After running the code, you will get the below result.
You can use the below code if you want to count your specific text row-wise. Insert the below code in your VBA module and run it.
For j = 1 To 12 'Adjust Rows
count = 0
For i = 1 To 2 Step 12 'Adjust Columns
If Cells(j, i) = "Apple" Then count = count + 1
MsgBox "Count of 'Apple's in row " & j & " is " & vbLf & count
After running the code, you will see that a text box appears which will tell you the number of time your text appears in the 1st row. After pressing Ok, it will tell you the number of appearance of that specific text in the 2nd row. It will continue to appear in total 12 times.
Download The Working File
In this article, you can see different formulas that can count cells with specific text. Hope you find this article interesting. This article will help you to find a specific data of your worksheet. Stay tuned for more articles.
- How to Count Frequency of Text in Excel (Using VBA)!
- The Different Ways of Counting in Excel
- Finding out the number of duplicate rows using COUNTIF formula