counting values ignoring duplicates

  • Thread starter Thread starter matt3542
  • Start date Start date
M

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
 
=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)
 
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
Click to expand...


Try this formula:

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

Hope this helps / Lars-Åke
 
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---
 
Wow, thanks for taking the time to give me so many options, this has been
incredibly helpful!
 
I think it's great to have a few options for doing this...just to confirm
that your results are correct...


Regards,
Ryan---
 
Hi Ryan, apologies for the delay replying, just to confirm all 5 solutions
worked perfectly, clever stuff, thanks so much again
Regards
Matt
 
Just curious:
Which of your 9 suggested solutions use the least computing power and
computes the fastest?
 
Back
Top