Skip to main content

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.


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:

  • The SUMPRODUCT function can be used to multiply two or more columns or arrays together, and then get the sum of products.
  • The SEARCH function returns the location of the specific character or text from the given text string.
  • 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

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates  |  Delete Blank Rows  |  Combine Columns or Cells without Losing Data  |  Round without Formula ...
Super VLookup: Multiple Criteria  |  Multiple Value  |  Across Multi-Sheets  |  Fuzzy Lookup...
Adv. Drop-down List: Easy Drop Down List  |  Dependent Drop Down List  |  Multi-select Drop Down List...
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  Compare Columns to Select Same & Different Cells ...
Featured Features: Grid Focus  |  Design View  |  Big Formula Bar  |  Workbook & Sheet Manager | Resource Library (Auto Text)  |  Date Picker  |  Combine Worksheets  |  Encrypt/Decrypt Cells  |  Send Emails by List  |  Super Filter  |  Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters ...)  |  50+ Chart Types (Gantt Chart ...)  |  40+ Practical Formulas (Calculate age based on birthday ...)  |  19 Insertion Tools (Insert QR Code, Insert Picture from Path ...)  |  12 Conversion Tools (Numbers to Words, Currency Conversion ...)  |  7 Merge & Split Tools (Advanced Combine Rows, Split Excel Cells ...)  |  ... and more

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.
Comments (2)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
this formula =SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$6, A2))) not work! pls update. i use excel from 2007 ofice.
This comment was minimized by the moderator on the site
Hello, mike,
The formula in this article works well in my Excel 2007. SUMPRODUCT and SEARCH are built-in functions in Excel 2007.
Could you insert a screenshot of your problem here? This way, we can identify where the issue is.

Thank you!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
Rate this post:
0   Characters
Suggested Locations