# 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
``````
-
Thank you for clearly stating (i) what you have, (ii) what you have tried and (iii) what you would like (and for voting!) - so few OPs do. – pnuts Dec 13 '12 at 16:47

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)
``````
-
Does this give the desired outcome? – Jacob Jan Tuinstra Dec 13 '12 at 5:25
@JacobJanTuinstra Yes (but requires something like Remove Duplicates afterwards!) :-) – pnuts Dec 13 '12 at 11:30
@JacobJanTuinstra Personally, though you may well (understandably!) feel a bit cheated I'd 'cut Sathya some slack' because (i) his answer was first (if not quite complete others are encouraged to enhance answers rather than adjust and repost as their own), (ii) it is elegant (1 function v your 6), (iii) Sathya is a very hard working moderator, (iv) how he manages to contribute as much as he does beats me and (v) he is brilliantly knowledgeable - so a 'pointer' from him will often be more valuable than a comprehensive answer from another. So even if you were 'robbed' it was in a good cause! – pnuts Dec 13 '12 at 12:12
@pnuts: thanks for your effort. Sathya: 1 Up. – Jacob Jan Tuinstra Dec 13 '12 at 12:54
@pnuts Thank you very much, I'm truly honoured and humbled by your comment :-) – Sathya Dec 13 '12 at 16:32

Use the following functions in microsoft excel:

• SUM
• IF
• FREQUENTY
• MATCH
• COUNTIF
• CONCATENATE

And prepare them like this:

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

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

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

-
The formula for 'Get all unique items' above counts how many rather than extracts the uniques so I think for Excel your answer 'falls at the first hurdle' - but I have not downvoted you (even though for that reason I think your answer does not deserve an upvote) to try to offset your 'concern' over @Sathya's answer. Shame Excel does not have =UNIQUE eh! :-) – pnuts Dec 13 '12 at 12:17