counting values ignoring duplicates

[matt3542]

Hi there,

I am trying to count the number of values in a column (A2:A217) ignoring
duplicated values. As an eg, applying this to the data below I would expect
the count value to be 3. Please can anyone help, many thanks, Matt

00013270
00018038
00018038
00018038
00018038
00018038
00018038
00018038
00018038
00018038
00018038
00023049
00023049
00023049

 

[Jarek Kujawa]

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

 

[Dave Peterson]

You can use a formula like:
=SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&""))
to count the number of unique entries in A1:A10.

You may want to look at Chip Pearson's site. He has lots of techniques to work
with duplicates:
http://www.cpearson.com/excel/Duplicates.aspx

 

[Bob Phillips]

=SUMPRODUCT((A2:A20<>"")/COUNTIF(A2:A20,A2:A20&""))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Lars-Åke Aspelin]

Hi there,

I am trying to count the number of values in a column (A2:A217) ignoring
duplicated values. As an eg, applying this to the data below I would expect
the count value to be 3. Please can anyone help, many thanks, Matt

00013270
00018038
00018038
00018038
00018038
00018038
00018038
00018038
00018038
00018038
00018038
00023049
00023049
00023049

Try this formula:

=SUM(1/COUNTIF(A2:A217,A2:A217))

Hope this helps / Lars-Åke

 

[matt3542]

Thanks very much, that worked perfectly

You can use a formula like:
=SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&""))
to count the number of unique entries in A1:A10.

You may want to look at Chip Pearson's site. He has lots of techniques to work
with duplicates:
http://www.cpearson.com/excel/Duplicates.aspx

 

[matt3542]

Thanks for the link, appreciated, will undoubtedly come in handy for future
queries

 

[matt3542]

Hi Bob, this also worked, many thanks, appreciated

 

[ryguy7272]

I can come up with at least 9 ways to do this; any more is moot:
=SUMPRODUCT((A1:A78<>"")/(COUNTIF(A1:A78,A1:A78)+(A1:A78="")))
=SUM(IF(FREQUENCY(IF(LEN(A1:A971)>0,MATCH(A1:A971,A1:A971,0),""),IF(LEN(A1:A971)>0,MATCH(A1:A971,A1:A971,0),""))>0,1))
(this is a CSE function; you must hit Ctrl + Shift + Enter at the same time)
=SUM(IF(A1:A400<>"",1/COUNTIF(A1:A400,A1:A400)))
(this is a CSE function; you must hit Ctrl + Shift + Enter at the same time)
=SUMPRODUCT((A1:A78<>"")/(COUNTIF(A1:A78,A1:A78&"")))
(this is a CSE function; you must hit Ctrl + Shift + Enter at the same time)
=SUM(--(FREQUENCY(IF(A1:A2676<>"",MATCH(A1:A2676,A1:A2676,0)),ROW(INDIRECT("1:"&ROWS(A1:A2676))))>0))
(this is a CSE function; you must hit Ctrl + Shift + Enter at the same time)

Regards,
Ryan---

 

[matt3542]

Wow, thanks for taking the time to give me so many options, this has been
incredibly helpful!

 

[ryguy7272]

I think it's great to have a few options for doing this...just to confirm
that your results are correct...


Regards,
Ryan---

 

[matt3542]

Hi Ryan, apologies for the delay replying, just to confirm all 5 solutions
worked perfectly, clever stuff, thanks so much again
Regards
Matt

 

[Gary]

Just curious:
Which of your 9 suggested solutions use the least computing power and
computes the fastest?

 

[Bernd P]

Hello,

Good question.

The OP seems to have less than 1000 records because the (IMHO) best
solution for more than let's say 5000 records has not been mentioned
so far.

A runtime comparison together with a better solution:
http://www.sulprobil.com/html/count_unique.html

Regards,
Bernd