Find the number of occurrence of a word in Excel 2007

How can I count the amount of occurrence in Excel 2007? The desired outcome should be something like:

apple 1
berry 2
coconut 3
donut 2
grape 1
lime 1

I have tried =COUNTIF($A:$A,A7), but that didn't gave the desired result.

Sample Data:

apple
berry
berry
coconut
coconut
coconut
donut
donut
grape
lime

microsoft-excel-2007

user1696748

Posted 2012-12-13T04:00:31.843

Reputation: 33

Answers

Replace A7 with the row number for each row.

apple   =COUNTIF($A:$A,A1)
berry   =COUNTIF($A:$A,A2)
berry   =COUNTIF($A:$A,A3)
coconut =COUNTIF($A:$A,A4)
coconut =COUNTIF($A:$A,A5)
coconut =COUNTIF($A:$A,A6)
donut   =COUNTIF($A:$A,A7)
donut   =COUNTIF($A:$A,A8)
grape   =COUNTIF($A:$A,A9)
lime    =COUNTIF($A:$A,A10)

Sathyajith Bhat

Posted 2012-12-13T04:00:31.843

Reputation: 58 436

Does this give the desired outcome? – Jacob Jan Tuinstra – 2012-12-13T05:25:28.140

@pnuts: So actually no, but after removal of duplicates it will be. – Jacob Jan Tuinstra – 2012-12-13T11:33:35.293

1@pnuts: thanks for your effort. Sathya: 1 Up. – Jacob Jan Tuinstra – 2012-12-13T12:54:35.510

1@pnuts Thank you very much, I'm truly honoured and humbled by your comment :-) – Sathyajith Bhat – 2012-12-13T16:32:38.130

@Jacob as pnuts mentioned, this answer was meant to be a pointer, since OP didn't make it clear whether the dupe entries were required or not, I thought I'll leave a first glimpse answer to see this is what the OP wanted. I didn't expect you to post another comprehensive answer, and I truly didn't expect the OP to accept this right away. Thanks. – Sathyajith Bhat – 2012-12-13T16:35:10.633

The OP is right. I'm glad I was part of the process. – Jacob Jan Tuinstra – 2012-12-13T16:57:27.613

Use the following functions in microsoft excel:

SUM
IF
FREQUENTY
MATCH
COUNTIF
CONCATENATE

And prepare them like this:

Get all unique items: SUM(IF(FREQUENCY(MATCH(A1:A10,A1:A10,0),MATCH(A1:A10,A1:A10,0))>0,1))
Count them: =COUNTIF($A$1:$A$10,B1)
Added them together: =CONCATENATE(B1," ",C1)

In Google Spreadsheet it would have been a bit easier:

Prepare all unique items: =UNIQUE(A1:A10)
Count them: =COUNTIF($A$1:$A$10,B1)
Added them together: =CONCATENATE(B1," ",C1)

See example I've prepared: Find the number of occurrence of a word

Jacob Jan Tuinstra

Posted 2012-12-13T04:00:31.843

Reputation: 353