Counting in Excel

[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.

 

[Jacob Skaria]

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

 

[Gord Dibben]

=SUMPRODUCT((FREQUENCY(A1:A10,A1:A10)>0)*1)

Returns 2 with your sample data.


Gord Dibben MS Excel MVP

 

[mrsmasterg]

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?

 

[mrsmasterg]

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.

 

[Gord Dibben]

Numbers or text........

This formula is from Chip Pearson's site.

http://www.cpearson.com/excel/Duplicates.aspx

=SUM(IF(FREQUENCY(IF(LEN(A1:A10)>0,MATCH(A1:A10,A1:A10,0),""),
IF(LEN(A1:A10)>0,MATCH(A1:A10,A1:A10,0),""))>0,1))

An array formula so must be entered using CTRL + SHIFT + ENTER


Gord

 

[christabbie]

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

 

[Dave Peterson]

So you're looking to count unique entries?

Maybe using this technique from Debra Dalgleish's site would work:
http://contextures.com/xlPivot07.html#Unique