< Go Back

Count keywords cell contains

To acquire the number of some specific keyword in a cell, SEARCH function for that specific keyword can be used to find it and make a count using some other support functions such as ISNUMBER and SUMPRODUCT functions.

Count Keywords Cell Contains

To count how many times a specific keyword is used in a cell. Following formula can be used in general. The detail of working of this formula is explained below.

Formula

=SUMPRODUCT(--ISNUMBER(SEARCH(keywords,Xn)))

Explanation

The above formula can be used to find the number of times a keyword is used in a cell. This formula incorporates 3 functions i.e. SUMPRODUCT, ISNUMBER, and SEARCH.

SEARCH function is used to search for a specific keyword in the selected cell “Xn” or some range of cells. If the specific keyword is found in any cell e.g. A1. It returns the position of the first character of the keyword found in that specific text. If the word is not found, it generates a #VALUE! error.

The ISNUMBER function returns TRUE the argument passed to it is a number, if not it returns FALSE.

The double negative sign is used to change the array of TRUE and FALSE entries into ones and zeros respectively.

Finally, SUMPRODUCT function is used to calculate the sum of the entries of the array in it, that returns the number of times the keyword has occurred in the text.

Example 1

Few sample sentences are considered in the form of an Excel sheet. Along with that, a separate array of keywords is formed, which contains a list of keywords that are to be found in the sentences.

Figure1. Sample sheet for extracting all partial matches from the data

Specify the keywords in the keywords array and to get the number of keywords to enter the following formula in the cell C3. And read the cell B3 in the SEARCH function.

=SUMPRODUCT(--ISNUMBER(SEARCH(keywords,B3)))

Figure2. Getting the count value for the matched results from the data

Now according to the formula, each sentence is checked for the 3 keywords separately and the corresponding count is provided in front of the sentence.

 

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar