Excel Question - Counting Unique entries in a column

Thundyrkatz

Hey, I have a long list of entries in a column. What I need to find out is how many of each entry I have and what is the entry.

So for example...

A
A
A
B
B
C

I would like a formula that would tell me that i have

A = 3
B = 2
C = 1

I could use the Countif formula for each instance, but i am hoping there is a way to streamline the process as there are many dozen different unique entries and i don't want to fill out dozens of countif formulas.

Is this possible?

Thanks in advance for your help!

Thundyrkatz

Ha! i just realized i would use the subtotal function! Duh! NM, this thread can be locked.

Rhesus Positive

I know a rather kludgy way.

1) Copy the list to a separate column
2) Remove duplicates in that column (in the Data tab)
3) You should now have a list of all the possible options in the list, but only showing up once. For sake of argument, let's say they're in column B, and the original list is in column A.
4) In cell C1, put the formula =COUNTIF($A$2:$A$250,B1). This will tell you how many times the value in B1 turns up in the list (assuming a list of 250 values).
5) Do the corner drag thing to put that formula next to each cell in column B.
6) Tally up the totals to make sure I haven't missed anything.

Hope that helps, but I'm sure somebody else will come along with a more elegant example which I will then steal for my own reports.

Rhesus Positive

Or the OP will.

wonderpug

A pivot table can do this lickety split. You pretty much just need to hit "insert pivot table" and spend 2 seconds clicking and dragging.

If you haven't dabbled in pivot tables before, it's well worth doing. Very powerful stuff. It's one of those things that can be a little intimidating at first glance, but is not at all hard to actually do.

Thundyrkatz

Thanks for the help guys. I am always interested in different ways to do stuff in excel.

Dr. Frenchenstein

PIVOT TABLES 4 LYFE

Natas_Xnoybis

gotta look at the pivot table thing, but this is what I came up with

=COUNTIF(I5:I34,I5)

where I5:I34 is the column you are looking for occurences off a number,

3 =COUNTIF(I5:I34,I5)
4 1
5 1
6 2
6 1
8 1
7 1
33235 1
14646 1
666 2
666 1

then just sort on anything >1

hmm that gets the job done, but that is pretty clunky heheh

wonderpug

Totally look into pivot tables! You'll impress the ladies (and more likely, your future employers).



It literally took me more time to take a screenshot and upload it to imgur than it did for me to create the pivot table.

Inquisitor77

I pivot tables, but every time someone talks about them this scene always pops into my head:

(Skip to 0:05)
http://youtu.be/w5DHU8SwYJ0

Thundyrkatz

No doubt Pivot tables are awesome, I had to teach myself how to use them last year and they definitely have this terror mystique to them. Which is totally undeserved as they are super easy to use. On the plus side if you say you can use them, people look at you like your some kind of sorcerer! haha.

Thanks again for the help, we data crunchers gotta stick together!

Natas_Xnoybis

hmm got the pivot table up, but can't figure out to do a count of occurances, just a "count" of records total...hmm

wonderpug

You need your types of values to go in the "rows" area so that it knows you want to categorize the counts.

SpongeCake

Inquisitor77 wrote: »

I pivot tables, but every time someone talks about them this scene always pops into my head:

(Skip to 0:05)
http://youtu.be/w5DHU8SwYJ0

This scene is a running joke in my office Pivot tables are ruined forever.

Cokebotle

=SUMPRODUCT(1/COUNTIF(A1:A10,A1:A10))

This also works if you don't want/can't use pivot tables. If you have blank cells you can use =SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&"")) to ignore blank cells.

Edit: Fixed formula error, but it looks like this isn't quite what you were looking for, sorry!