Count keywords cell contains based on a list
If you want to count the number of keywords appears in a cell based on a list of cells, the combination of the SUMPRODUCT, ISNUMBER and SEARCH functions may help you to solve this problem in Excel.
Count keywords cell contains based on a list
For example, I have a list of text strings in column A, and some keywords in column D as below screenshot shown, now, to get the number of keywords which a cell contains in column A based on the data in column D, please do with the following formula.
1. Please enter or copy the following formula into a blank cell:
2. Then, drag the fill handle to copy this formula to other cells, and the number of the keywords within a cell has been calculated, see screenshot:
Explanation of the formula:
- SEARCH($D$2:$D$6, A2): is used to return the position of the keywords in the text string when found, if not found, the #VALUE! error will display;
- --ISNUMBER(SEARCH($D$2:$D$6, A2)): if the keyword is found in the text string, SEARCH function returns the position number, the ISNUMBER function returns TRUE; if doesn’t find the keyword in the text string, SEARCH function returns #VALUE! error, and the ISNUMBER function will return FALSE; And the double hyphens (--) will change the TRUE or FALSE to 1 or 0.
- SUMPRODUCT: is used to sum the array of 1 and 0.
Notes:
1. If a keyword appears multiple times in a cell, it will be counted only once.
2. If there are blank cells in the keywords list cells, the above formula will not get the correct result, in this case, you should apply the below array formula:
Please remember to press Ctrl + Shift + Enter keys together to get the correct result, see screenshot:
Relative functions used:
- SUMPRODUCT:
- The SUMPRODUCT function can be used to multiply two or more columns or arrays together, and then get the sum of products.
- SEARCH:
- The SEARCH function returns the location of the specific character or text from the given text string.
- ISNUMBER:
- TheISNUMBER function returns TRUE when a cell contains a number, and FALSE if not.
More articles:
- Count lines which are separated by line breaks in a cell or a range
- This article is talking shout how to count the number of lines which are separated by line breaks in a cell or a range of cell by using formula in Excel.
- Extract multiple lines from a cell
- If you have a list of text strings which are separated by line breaks (that occurs by pressing Alt + Enter keys when entering the text), and now, you want to extract these lines of text into multiple cells as below screenshot shown. How could you solve it with a formula in Excel?
- If cell contains text then display in Excel
- If you have a list of text strings in column A, and a row of keywords, now, you need to check if the keywords are appear in the text string. If the keywords appear in the cell, displaying it, if not, blank cell is displayed as following screenshot shown.
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
