How to Count the Number of Times a Word Appears in a Range?

In this article, we'll look at a straightforward yet effective function Excel offers to quickly count the instances of a particular term inside a given range. When working with enormous datasets or when you need to execute data analysis activities, counting the occurrences of a term in a range can be immensely helpful. Excel's built-in functions can help you save time and effort when performing tasks like survey response analysis, measuring the frequency of terms in a list, or counting certain keywords in a document.

Using the COUNTIF function, we'll walk you through the process of counting the instances of a word in a range throughout this tutorial. This function gives you the precise number of instances that match your chosen criteria by allowing you to select a range of cells and a criterion to match.

Count the Number of Times a Word Appears in a Range

Here, we can complete the task using the formula directly in Excel. So let us see a simple process for knowing how you can count the number of times a word appears in a range in Excel.

Consider an Excel sheet where you have a range of text strings similar to the below image.

First, click on an empty cell in the case cell C2 and enter the formula as =SUMPRODUCT((LEN(A2:A6)-LEN(SUBSTITUTE(A2:A6,"Excel","")))/LEN("Excel")) and click enter to complete the task.

Empty cell > Formula > Enter.

In the formula, A2:A5, is the range of cells, and KTE is the word we are counting.

This is how you can count the number of times a word appears in a range in Excel.

Conclusion

In this tutorial, we have used a simple example to demonstrate how you can count the number of times a word appears in a range in Excel to highlight a particular set of data.