One common requirement when working with large datasets is counting how many times a particular word or value appears.
You may need to know how many times the word appears in a cell, a column, or an entire worksheet.
This problem can be of two types. You might need to count how many cells contain a given word, or how many times a given word appears in a cell.
In this tutorial, we will see both types of problems and different ways to solve them, including the use of VBA.
Count a Specific Word in a Range using COUNTIF
The COUNTIF function’s main task is to count the number of times a condition is met.
One of the most common uses of this function is to match a particular value to cell values in a range. This value can be a string or a number.
The syntax for the COUNTIF function is:
range is the range of cells you want to match and count fromcondition is the condition that must be met in order to count a cell as a match
What the above function will do is search for all the cells in the given range and count all the cells that match the given condition. Once it is done searching through all cells in the range, it will return the number of matched cells.
Let us apply this function to a simple example. Say we want to count the number of times the name ‘Peter’ appears in the range A2:A10 as shown below:
Note: If you can’t see the Developer ribbon, from the File menu, go to Options. Select Customize Ribbon and check the Developer option from the Main Tabs. Finally, Click OK.
The great thing about this method is that it does not matter whether you select one cell or a range of cells. It works both ways.
Here are a few ways you can tweak the above code to suit your requirements:
You can customize this code by changing the search_word value in line 3 to the word that you want to count. So if you want to count the number of times the word “because” appears, you can replace this line with: search_word = ”because”You can use a cell reference instead of specifying the actual word to search in the code itself. So, if you want to use a cell reference, say A6, instead of specifying the search word “happy”, you can replace line 3 with: search_word = Cells(6,”A”).ValueIf you want the search to be case-insensitive, you can use the LCase function. So, you will need to change line 8 to: Count = Count + ((Len(cell) – Len(Replace$(LCase(cell), LCase(search_word), “”))) / Len(search_word))
In this tutorial, we demonstrated how you can count the number of times a word appears in Excel. We showed how to count the exact occurrences of a word in a range of cells using COUNTIF.
We then showed you how you can count how many times a word appears inside the string of a single cell, using SUBSTITUTE and LEN functions.
After that, we demonstrated how you can apply this to a whole range of cells containing strings, by just wrapping the same formula in a SUMPRODUCT function.
Finally, we provided a VBA code snippet to let you get this task done quickly.
We made this tutorial keeping in mind the different kinds of issues you might face when trying to count occurrences of a word in your worksheet. We do hope this was helpful.