Forum Discussion
HelpGeorge
Jul 06, 2021Copper Contributor
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
Sort By
- LorenzoSilver Contributor
Alternatively, with data in A1:A15
in C1:
=LET( range, A1:A15, unique, UNIQUE(range), CHOOSE({1;2}, unique, COUNTIF(range,unique)) )
- Subodh_Tiwari_sktneerSilver ContributorThe 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.
- Marcia1720Copper Contributor=COUNTIF(A$2:A$10376,C3) copy formula downn
Or: Try Pivot Table- HelpGeorgeCopper ContributorThank you!
- Subodh_Tiwari_sktneerSilver ContributorYou'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.