Count in column

[sgm1911]

I have a column of data:

Apple
Orange
Apple
Apple
Orange
Pear

How would I get excel to count and list out:
3 apple
2 orange
1 pear

I know how to use countif, but that is if I am searching from a list of data.

Sometimes there might be other unknown data inside, so I need to count and list out everything inside the column.

Apple
Orange
Apple
Apple
Orange
Pear
Abcdefg

3 Apple
2 Orange
1 Pear
1 Abcdefg

 

[AlanY]

first, create a unique list in Col B then count them in Col C

Excel 2013/2016
	A	B	C
1
2	Apple	Apple	3
3	Orange	Orange	2
4	Apple	Pear	1
5	Apple	Abcdefg	1
6	Orange
7	Pear
8	Abcdefg
6

Cell Formulas
Range	Formula
B2	{=IFERROR(INDEX($A$2:$A$8, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$8), 0)),"")}
C2	{=COUNTIF($A$2:$A$8,B2)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[sgm1911]

Thanks, but I would like to do this without a unique list, because sometimes I wouldnt know all the values in Column A.

Is there a way to do this? Macro perhaps?

 

[AlanY]

Thanks, but I would like to do this without a unique list, because sometimes I wouldnt know all the values in Column A.

Is there a way to do this? Macro perhaps?

not quite sure what you're after.
both your examples are with a unique list in it

I have a column of data:

Apple
Orange
Apple
Apple
Orange
Pear

How would I get excel to count and list out:
3 apple
2 orange
1 pear

I know how to use countif, but that is if I am searching from a list of data.

Sometimes there might be other unknown data inside, so I need to count and list out everything inside the column.

Apple
Orange
Apple
Apple
Orange
Pear
Abcdefg

3 Apple
2 Orange
1 Pear
1 Abcdefg

 

[jtakw]

Thanks, but I would like to do this without a unique list, because sometimes I wouldnt know all the values in Column A.

Is there a way to do this? Macro perhaps?

Hi,

I think you misunderstood, Alan Y's Post #2 , the formula for B2 (copied down, don't forget to CSE) will provide the "unique" list, you Don't need to know all the items in column A. Then use his formula in C2 (actually, no CSE needed for this formula) copied down to give you the Count of the unique items. Try it to see.