Counting Duplicates Once

K

kevin giblin

following on from the counting of multiple selections once, how do excel to count a a list of card number with duplicates once.

For exapmle a list of card numbers would look like this

Card number

11111 22222 33333
11111 22222 33333
11111 22222 33333
44444 55555 66666

the aswer would be two as two once card number has been inserted 3 times?

EggHeadCafe - Software Developer Portal of Choice
ADO.NET Programmer's Reference
http://www.eggheadcafe.com/tutorial...e35-430fa3022e03/adonet-programmers-refe.aspx
 
D

Domenic

following on from the counting of multiple selections once, how do excel to
count a a list of card number with duplicates once.

For exapmle a list of card numbers would look like this

Card number

11111 22222 33333
11111 22222 33333
11111 22222 33333
44444 55555 66666

the aswer would be two as two once card number has been inserted 3 times?

EggHeadCafe - Software Developer Portal of Choice
ADO.NET Programmer's Reference
http://www.eggheadcafe.com/tutorials/aspnet/fadf376e-ec80-4054-ae35-430fa3022e
03/adonet-programmers-refe.aspx


Try the following formula, which needs to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER...

=SUM(IF(FREQUENCY(IF(A2:A10<>"",MATCH("~"&A2:A10,A2:A10&"",0)),ROW(A2:A10
)-ROW(A2)+1),1))

Adjust the ranges, accordingly.
 
T

T. Valko

One way...

=SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&""))

Note that this is slow to calculate on large ranges. ~1000+ rows
 
R

ryguy7272

There are so many ways to do this:
< -- =SUMPRODUCT((A2:A105<>"")/(COUNTIF(A2:A105,A2:A105&"")))
< -- =SUMPRODUCT((A2:A998<>"")/(COUNTIF(A2:A998,A2:A998)+(A2:A998="")))
< --
=SUM(IF(FREQUENCY(IF(LEN(A2:A999)>0,MATCH(A2:A999,A2:A999,0),""),IF(LEN(A2:A999)>0,MATCH(A2:A999,A2:A999,0),""))>0,1))
Note: This is a CSE Function
< -- =SUMPRODUCT((A2:A105<>"")/COUNTIF(A2:A105,A2:A105&"")*(A2:A105<>""))
< -- =SUMPRODUCT(--(A2:A999<>""),1/COUNTIF(A2:A999,A2:A999&""))
< --
=SUM(--(FREQUENCY(IF(A2:A2705<>"",MATCH(A2:A2705,A2:A2705,0)),ROW(INDIRECT("1:"&ROWS(A2:A2705))))>0))
Note: This is a CSE Function
< -- =COUNT(1/FREQUENCY(A1:A400,A1:A400))
< -- =SUM(IF(A2:A400<>"",1/COUNTIF(A2:A400,A2:A400)))
< --
=SUM(IF(FREQUENCY(IF(A2:A1001<>"",MATCH("~"&A2:A1001,A2:A1001&"",0)),ROW(A2:A1001)-ROW(A2)+1),1))

HTH,
Ryan---
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top