Forum Discussion

HelpGeorge's avatar
HelpGeorge
Copper Contributor
Jul 06, 2021
Solved

Find the frequency for how many times each unique word is listed

Hi, I have a list of text that is 10,372 rows.

 

Using the formula: =COUNTA(UNIQUE(A2:A10376)) , I have found that there are 1672 unique words, and using the formula: =UNIQUE(A2:A10376) have a  text list of the unique words.

 

Is there a way / formula to see how many times each unique word is listed (i.e. the frequency of each word)?

 

I am hoping to avoid manually searching for each unique word!

 

Thank you.

  • The easiest method would be inserting a Pivot Table and then drag the Topics field in the Row area and drag the same Topics field again in the Values area. That would show you a report of all the unique Topics and their counts.

5 Replies

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor

    HelpGeorge 

    Alternatively, with data in A1:A15

     

    in C1:

    =LET(
        range,  A1:A15,
        unique, UNIQUE(range),
        CHOOSE({1;2}, unique, COUNTIF(range,unique))
    )
  • The easiest method would be inserting a Pivot Table and then drag the Topics field in the Row area and drag the same Topics field again in the Values area. That would show you a report of all the unique Topics and their counts.
  • Marcia1720's avatar
    Marcia1720
    Copper Contributor
    =COUNTIF(A$2:A$10376,C3) copy formula downn
    Or: Try Pivot Table
      • Subodh_Tiwari_sktneer's avatar
        Subodh_Tiwari_sktneer
        Silver Contributor
        You're welcome!
        If that takes care of your original question, please take a minute to accept the post with the proposed solution as the Best Response to mark your question as Solved.

Resources