Log in  \/ 
x
or
x
x
Register  \/ 
x

or

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:

=SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$6, A2)))

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:

=SUMPRODUCT(--ISNUMBER(SEARCH(IF($D$2:$D$6<>"",$D$2:$D$6),A2)))

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:

  • 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

Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300 powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) and save 80% time for you.

  • Designed for 1500 work scenarios, helps you solve 80% Excel problems.
  • Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
  • Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
  • 30-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years.
Ribbon of Excel (with Kutools for Excel installed)

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, Firefox, And New Internet Explorer.
Screen Shot of Excel (with Office Tab installed)
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.