Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
share|improve this question
    
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
add comment

2 Answers

up vote 3 down vote accepted

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)
share|improve this answer
    
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
1  
@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
1  
@pnuts: thanks for your effort. Sathya: 1 Up. –  Jacob Jan Tuinstra Dec 13 '12 at 12:54
1  
@pnuts Thank you very much, I'm truly honoured and humbled by your comment :-) –  Sathya Dec 13 '12 at 16:32
show 3 more comments

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)

In Google Spreadsheet it would have been a bit easier:

  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

share|improve this answer
1  
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.