Excel count no of words occurs in column

[Vindhyachal.takniki]

1. I have excel column as below:



2. Need to show in excel how many times each individual word appears like
One: 2
Two: 2
Three: 1
Five: 2

3. How to do it?

[Ian.M]

*One* way of doing it is like this.

How it works:
In the column uniques apart from the first line (which is just the data value), there is a MATCH function that checks if the data value is found anywhere above that line (note the slightly tricky array reference with the first row absolute).   If not, it will return error value N/A.  The IFNA function tests for that error value, and either returns the data or an empty string. 

In the column count, if  uniques is non-blank not an empty string, it uses the COUNTIF function to count the number of occurrences of it within the data

[mariush]

If the number of words is very small, you could have a vertical column for each possible word somewhere far to the right where you put 1 or 0 if the cell has that word

ex  =IF(A2="One",1,0)

Then you can have a summary 

One  |  =SUM(J1:K100)
Two | = SUM(K1:K100)
Three | = SUM(K1:L100)

.. and so on

[PA0PBZ]

[Vindhyachal.takniki]

there are around 500 such words in a column, which may or may not be repeat.
Checking for individual would be diffcult.

Is there no such formula which can count each unique word?

[PA0PBZ]

After applying the formula you can convert the formulas into values (In the example above right click column B, Copy, Paste as values) and then select column A and B and remove duplicates (on the Data menu). That will leave you with a single row for each value (word).

[geggi1]

the simplest way is to use the COUNTIF function.
Here is a quick tutorial. https://www.w3schools.com/excel/excel_countif.php

I use this function a lot in my spreadsheets.

[Ian.M]

Yes. That's what I did back in reply #1.   The problem is to auto-populate the list of unique words, which I did by determining if the word did not appear on any line above it, which gave me the unique word next to its first occurrence, with its count next to that.  If you need to eliminate the blank lines, you could try filtering on the unique word being not "".