CODEWITHSUNDEEP
excel
user1799724
user1799724

Reputation: 1531

Excel Formula - Count the frequency of text values in a column

Let's say I have different text values in a column such as:

Column A
(source: sartodimoda.com)

I want a formula to calculate the frequency of every text value in the column. So the answer should be displayed in two columns. Column D should be the text value and column E should be the frequency of occurrence - so it should come out something like this -

Column D & E
(source: sartodimoda.com)

Thank you so much for your help.

Upvotes: 15

Views: 52291

Answers (2)

xeloq
xeloq

Reputation: 59

I faced to the same problem a while ago, and the only way for me was a "Pivot Table", though adding such function in "SUBTOTALS" would be very handy:

  1. Add a name ("Searched Fields") for the searched range, otherwise the first cell should be treated as one
  2. Select the range of your texts (including the one containing "Searched Fields")
  3. Insert -> Pivot Table -> OK
  4. In "Pivot Table Fields List" Frame from the sub-frame "Choose fields to add to report" drag the only item "Searched Fields" to sub-frames "Row Labels" and "∑ Values", and you'll get what you want:

Enjoy!

Upvotes: 5

Nicholas Flees
Nicholas Flees

Reputation: 1953

As @Kabir said in the comments, you could use a pivot table. You could also use the COUNTIF function. Try putting the following formula in cell E1 and dragging it downward so the other cells in column D.

=COUNTIF(A:A,D1)

Upvotes: 13

Related Questions