count similar words

Hello everyone,

Wondering how can I count similar words if I have the following data:

Column A:
dropbox
icloud, googledrive
google drive
Microsoft OneDrive
Drop box
Google
Google Docs and OnlyOffice
google drive
google
dropbox, google drive
Dropbox, OneDrive
Google docs, dropbox
Calibre Content Server Amazon Photo Prime DropBox
Google Drive, MEGA
Google drive, yahoo mail and earlier dropbox.
I use the apple cloud and google's cloud service.
When I do, usually DropBox
Dropbox, icloud

And I would like to have the output like this:
Column B Column C
dropbox 8
icloud 2
google 10 --- should count all word occurrences like: googledrive, google drive, google, google docs
docs 1
etc..

I just need to count how many words in this list to understand the stats on each of them. I would appreciate your tips on how to approach this with a formula or a macros.

Thanks in advance!

Try this ...

=COUNT(SEARCH(B1,A1:A1000))

Enter with Ctrl+Shift+Enter.

Originally Posted by Phuocam

Try this ...

=COUNT(SEARCH(B1,A1:A1000))

Enter with Ctrl+Shift+Enter.

Thanks for your response, but I receive the error https://sc-cdn.scaleengine.net/i/6cc...e6bbb351e5.png

B1= dropbox

B2 = icloud

B3 = ...

Formula in C1

Originally Posted by shukla.ankur281190

or Try

C1

Formula:

Please Login or Register  to view this content.

Thanks for your response. I'm sorry, but it looks like this doesn't work too. Please kindly see the attached file of sample data.
Thanks in advance!

Originally Posted by shukla.ankur281190

Try

B1

Formula:

Please Login or Register  to view this content.

Drag down!!

Thanks! This worked. Is there any way to group similar words and count them? I would like to know how many users use dropbox, icloud, etc.

See attached:

in C1

=SUMPRODUCT(--ISNUMBER(SEARCH($B1,$A$1:$A$100)))

Originally Posted by JohnTopley

See attached:

in C1

=SUMPRODUCT(--ISNUMBER(SEARCH($B1,$A$1:$A$100)))

Thanks JohnTopley! I have a few thousands lines in the original document and there are other brand names which I would like to count and not to miss. Is there any way to count all such words without manually pasting the names?
Thanks in advance!

No ... you need to know which words to match .... how else can it be done?

I would appreciate it if anyone could point me to a solution which would allow me to count all such words without manually pasting the names? Any Macros or keyword density checker formulas?

Thanks in advance for any tips!

The only starting option is to extract ALL words (many of which may not be relevant for your analysis) and then filter these.