Search code examples
google-sheetsgoogle-sheets-formula

How to find number of occurrences of a table of key words in a column of strings in Google Sheet

In Column A, I have a list of key words separated by rows, such as

key1
key2
key3
key4

They are all string values -- literally "key1", "key2", etc.

In Column B, I have a mix of words that could be key words in Column A, each word separated by rows, such as

word1
word2
key2
word3
key3
word1
word4
key3
word5
key1
word3

Question:

How do I count the number of occurrences of key words from Column A in Column B in one formula?

If a method happens to involve generating an array of occurred key words in Column B, could the answer explain how to do that too please? Thanks

Solution

  • The simplest solution is to use FILTER:MATCH.

    FILTER(B1:B20,MATCH(B1:B20,A1:A4,0))

    • match the COLUMN B to COLUMN A range. Then just use COUNTA in the filtered result.

    Counted Result