count no of occurence per item

[okl]

A1: apple
A2: apple
A3: orange
A4: apple
A5: grape
A6: orange
A7: grape

apple 3
orange 2
grape 2

how can i achieve such result?

[Paul]

Hello Okl,

What version of Excel are you using? And are you required to use VBA code (programming), or will worksheet functions suffice?

[okl]

excel 2003, function will do.

thanks

[Paul]

Ok, good.

1. Select your data in column A (you may want a header in A1, like "Fruit Type")
2. Click Data -> Filter -> Advanced Filter
3. Select 'Copy to another location' and next to 'Copy To', type in or select another cell (like B1)
4. Check the box for 'Unique Values Only'
5. Click OK

That will create a list of unique values from column A, starting in B1. To get the counts of each, you can then use a formula in C2 such as:

=COUNTIF($A:$A,B2)

Finally, double-click the lower right corner of C2 and the formula will automatically fill itself down to the last unique value in column B.

Hope that helps!

[okl]

solved, thanks!