How to count cells which contain text in Excel

If you are trying to calculate number of cells/observations in a particular column in your dataset which contains a particular text, then this guide will surely help you. So the below snapshot gives a problem set that we are trying to solve. Column B is the target column and we need to find the count of cells which contain a particular text. So we are going to use the countif function which basically counts the number of observations given a specific condition. So we just need to specify the range of the column and the condition. Now let’s see the following examples to understand it better.

1. The first task is to count number of cells which contain the string square : For this the formula will be = COUNTIF(B2:B13,”*square*”). The reason behind the “*” is that it will count all cells which contain square as a string even if there are any prefixes or suffixes. You can remove the * when you see that the string you are trying to count is the only word present in that cell. Otherwise you will have to use *.
2. The second task is to count number of cells which contain t : similarly the formula will refer to the same range. However, the string will be “t” in this case. So the formula will be =COUNTIF(B2:B13,”*t*”)
3. The third task is to again count the numbers of cells which contain the string “sq“. So the formula will be = COUNTIF(B2:B13,”*sq*”). Note here the formula counted cells which contain “square” as well as which contain “sq” because we put the “*”.

Now what if we have a slightly complicated scenario. Suppose we want to count the number of cells which contain any of the words or strings from a list of texts. For this case also we can use countif function but with a slight twist. As you can see in the example below the task is to find out the number of cells which contain the name “Harry” or “Steve”. So we are going to create a column, Flag containing either 1 or 0 to indicate the presence or absence of the names respectively. The formula for this will be

=IF(SUM(COUNTIF(K2,{“Steve“, “Harry“})),1,0)

Simply drag the formula down and then count the number of 1s in the column.

Hope you found this formula useful for your project. Happy learning 🙂