Finding the count of a string snippet appearing in multiple rows in excel

The excel powerful countif function allows you to pull the exact number of occurrences of a text snippet appearing in multiple rows of text

I often find that in my SEO work that I need to analyze large sets of data and in particular locating a string snippet that appears within a large string. Also finding the occurrence of the substring in a large pool of data such as many rows or columns.

Here I present a useful excel function called COUNTIF which returns the number of times a string snippet appears in multiples cells containing the larger string. For example, lets say, I have the following table shown..

Excel countif function and how to use it

Here go to an empty cell and type the following function..

=COUNTIF(A2:A34,”*.htm”)

A2:A34 refers to the range of cells within the column shown

“*.htm” refers to the snippet string that it needs to locate

This gives me a count of all the matching string which is the string that ends with ‘.htm’ and one will get a count of 28. If you change the snippet text to ‘*.html’, this means locate strings that end with ‘.html’ then the count returned is 3 which are the exact number of occurrences shown in the table above.

There are other ways where you can highlight the string, but more on this later.

Leave a Reply

Your email address will not be published. Required fields are marked *