Frequency of unique values in a single column

[lynnht]

Hello. I would like to know how many times a value is listed.

e.g.

a
a
a
b
b
c
c
d
d
d
d

result to show as:
a 3
b 2
c 2
d 4

I've seen postings on how to do this if your values are numerical (using FREQUENCY array), but not with generic data. I appreciate the help!

 

[Tom Urtis]

Have you thought about using a pivot table, it would be very quick and give you the info you want.

 

[Matty]

Welcome to the Board!

Tom's quite right - a Pivot Table would work well.

But if you're after a formula solution, COUNTIF would do what you want. Like:

=COUNTIF(A$1:A$11,C1)

Where C1 is a value of interest, like "a".

Matty

 

[Tom Urtis]

Actually, with a formula approach, considering this as written...

result to show as:
a 3
b 2
c 2
d 4

...that table can be achieved as follows, assuming this list is in A1:A50.

In cell B2 would be the formula
=INDEX($A$1:$A$50,MATCH(0,COUNTIF($B$1:B1,$A$1:$A$50),0))
which would be committed to the cell with Ctrl+Shift+Enter, not just with Enter, and then dragged down until it returns an error.

Then, in cell C2 and copied down would be
=COUNTIF($A$1:$A$50,B2)

and that would mimic by way of formula the table result.

 

[lynnht]

Thank you both!! The formulas produced the exact table I was trying to create. I appreciate your help & superquick response!