ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Count how many times the same word appears in column (https://www.excelbanter.com/excel-discussion-misc-queries/22292-count-how-many-times-same-word-appears-column.html)

Alexa

Count how many times the same word appears in column
 
Hello,

I have a long list of about 50,000 words in column A. Some of these
words are the same.

I want to know the exact number of times each word appears in the A
column.

And I want to sort these words based on how many times they appear in
the A column.

How can I do that?

Alexa

ExcelBanter AI

Answer: Count how many times the same word appears in column
 
Hi Alexa,

You can easily count how many times the same word appears in a column in Microsoft Excel by using the COUNTIF function. Here are the steps:
  1. Select the cell where you want to display the count of the first word.
  2. Type the formula
    Formula:

    =COUNTIF(A:A,A1

    in the formula bar and press Enter. This will count how many times the word in cell A1 appears in column A.
  3. Copy the formula down to the rest of the cells in the column by dragging the fill handle (the small square at the bottom right corner of the cell) down to the last cell you want to count.
  4. Now you have the count of each word in the column. You can sort the words based on how many times they appear by selecting the entire table (including the headers), going to the Data tab, and clicking on the Sort button. Choose the column with the counts as the sorting criteria and select either ascending or descending order.

That's it! You now have a sorted list of words with their corresponding counts. Let me know if you need any further assistance.

ΊΌΦέΘΛ

=countif(A:A,B1),if B1= you word



Bob Phillips

In B1, add =COUNTIF($A:$A,A1)
Copy down

Then sort by column B

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Alexa" wrote in message
om...
Hello,

I have a long list of about 50,000 words in column A. Some of these
words are the same.

I want to know the exact number of times each word appears in the A
column.

And I want to sort these words based on how many times they appear in
the A column.

How can I do that?

Alexa




[email protected]

Hi Bob,

Excel displays an error when I type in the formula. What should I do?

Alexa


RagDyeR

What's the error?
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

wrote in message
oups.com...
Hi Bob,

Excel displays an error when I type in the formula. What should I do?

Alexa



Bob Phillips

Hi Alexa,

The formula works okay for me.

Give us some details of the way you added it, the error and your data.

--

HTH

RP
(remove nothere from the email address if mailing direct)


wrote in message
oups.com...
Hi Bob,

Excel displays an error when I type in the formula. What should I do?

Alexa





All times are GMT +1. The time now is 04:50 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com