Count number of words refer to the list in a cell

**chaselok** · 01-30-2018, 12:55 AM

Hi all, anyone can solve this question?

Suppose I have two lists, namely WordList1 and WordList2, each containing some keywords I defined. For example, abc1, abc2, ... and abc5 belongs to WordList1

I want to write an excel function to count how many occurrence of keywords from the same group appears.

For example, "abc1 abc1 iiiiijjjkkkk" countains two "abc1", therefore WordCount1 (refer to WordList1) is 2.

f.JPG

**Phuocam** · 01-30-2018, 01:10 AM

Try:

=SUMPRODUCT(COUNTIF(A9,"*"&B$2:B$6&"*"))

**shukla.ankur281190** · 01-30-2018, 02:20 AM

Try

B9

Formula:

Please Login or Register  to view this content.

Copy across!

**samba_ravi** · 01-30-2018, 02:29 AM

Originally Posted by shukla.ankur281190

Try

B9

Formula:

Please Login or Register  to view this content.

Copy across!

Ankur, you are complicating the formula, Phuocam is good one, if you see the example of op "abc1abc2eee4eee4", here there is no space between words even though he wants the counts. So there is no need of substitute

**shukla.ankur281190** · 01-30-2018, 02:45 AM

Siva, Sorry but I still don't believe what OP asked for, Phuocam formula does. I have given solution as per his expected result

I am agree that substitute made this formula scary but trust me I am doing what I have learned you and others as well

Have a great day

Regards,
Ankur

**chaselok** · 01-30-2018, 06:03 AM

Thank you but the result is not the same as what I want.

For example, "abc1 abc1 iiiiijjjkkkk" countains two "abc1", therefore WordCount1 (refer to WordList1) is 2, not 1.