3

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
Improve this question
0

2 Answers 2

Reset to default
3

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)
Improve this answer
5
  • Does this give the desired outcome?
    – Jacob Jan Tuinstra
    Dec 13, 2012 at 5:25
  • @pnuts: So actually no, but after removal of duplicates it will be.
    – Jacob Jan Tuinstra
    Dec 13, 2012 at 11:33
  • 1
    @pnuts Thank you very much, I'm truly honoured and humbled by your comment :-)
    – Sathyajith Bhat ♦
    Dec 13, 2012 at 16:32
  • @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 ♦
    Dec 13, 2012 at 16:35
  • The OP is right. I'm glad I was part of the process.
    – Jacob Jan Tuinstra
    Dec 13, 2012 at 16:57
2

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

Improve this answer
0

You must log in to answer this question.

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