Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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:

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
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
@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

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
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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .