Counting Duplicates Once
 

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...mers-refe.aspx

Counting Duplicates Once
 

In article , kevin giblin wrote:

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...e35-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,A 2:A10&"",0)),ROW(A2:A10
)-ROW(A2)+1),1))

Adjust the ranges, accordingly.

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

Counting Duplicates Once
 

One way...

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

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

--
Biff
Microsoft Excel MVP


<kevin giblin wrote in message ...
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...mers-refe.aspx

Counting Duplicates Once
 

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


Gord Dibben MS Excel MVP

On Tue, 24 Nov 2009 10:32:03 -0800, kevin giblin wrote:

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...mers-refe.aspx

Counting Duplicates Once
 

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:A10 01,A2:A1001&"",0)),ROW(A2:A1001)-ROW(A2)+1),1))

HTH,
Ryan---


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Domenic" wrote:

In article , kevin giblin wrote:

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...e35-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,A 2:A10&"",0)),ROW(A2:A10
)-ROW(A2)+1),1))

Adjust the ranges, accordingly.

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions
.