Counting in Excel

  • Thread starter Thread starter mrsmasterg
  • Start date Start date
M

mrsmasterg

I have a spreadsheet in which duplicate data appears multiple times in
groups. I would like to count each occurence. Ex.
12345
12345
12345
12345
78912
78912
78912
78912
78912

expected result is that each occurence is counted once.
12345 occurs once
78912 occurs once

I do not want to use a pivot table because each 'group' has multiple rows of
data that needs to be shown.
 
1. Suppose you have the numbers in ColA. Assign a header to the column.
Select the range in Col A including the header.
2. From menu Data>Filter>Advanced Filter>Copy to another location
3. In copy to I have selected D1 and check 'Unique records only'
4. Click OK will give you the unique list in Col D

If this post helps click Yes
 
=SUMPRODUCT((FREQUENCY(A1:A10,A1:A10)>0)*1)

Returns 2 with your sample data.


Gord Dibben MS Excel MVP
 
My apologies. It did not work on my original problem. I did not mention
that the numbers contain dashes (social security numbers). Does this make a
difference?

Thanks for your tip, though! I was able to apply it to another problem that
contained only numbers. Do you have any other suggestions since I've
provided additional information?
 
Thanks for the great tip! It's not quite what I'm looking for to solve this
particular problem, however, it's something I can definitely use.
 
I would like to accomplish this same thing in a pivot table. Any advice on
doing that - if I can piggy-back on your question? :)

Christa
 
Back
Top