I have a range of cells (A5:A100). In some cells there are duplicate part
numbers. I want to count ONLY the unique values and not the duplicates. Does
anyone know how this can be done and what formula I would use to accomplish
this?
Thank you very much in advance, Karen

One way:

=SUMPRODUCT((A5:A100<>"")/COUNTIF(A5:A100,A5:A100&""))

If there are no empty cells within the range then you can reduce that to:

=SUMPRODUCT(1/COUNTIF(A5:A100,A5:A100))

--
Biff
Microsoft Excel MVP


"Karen" <(E-Mail Removed)> wrote in message
news:92E8E72B-5E7C-4051-B097-(E-Mail Removed)...
>I have a range of cells (A5:A100). In some cells there are duplicate part
> numbers. I want to count ONLY the unique values and not the duplicates.
> Does
> anyone know how this can be done and what formula I would use to
> accomplish
> this?
> Thank you very much in advance, Karen

Hello,

There is a bunch of possible solutions:
http://sulprobil.com/html/count_unique.html

Regards,
Bernd

This will check the whole column, but you can restrict it to just the range
you've noted if you want.

=SUM(IF(FREQUENCY(A:A,A:A)>0,1))

--
"Actually, I *am* a rocket scientist." -- JB
(www.MadRocketScientist.com)

Your feedback is appreciated, click YES if this post helped you.


"Karen" wrote:

> I have a range of cells (A5:A100). In some cells there are duplicate part
> numbers. I want to count ONLY the unique values and not the duplicates. Does
> anyone know how this can be done and what formula I would use to accomplish
> this?
> Thank you very much in advance, Karen

Thanks BerndP. That's pretty neat. I've seen other ways too. This list is
pretty comprehensive, I think:
< -- =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(1/COUNTIF(C13:C17,C13:C17))
Note: This is a CSE Function & it fails if there are any blanks in between
< -- =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))

I don't believe this list is exhaustive. There are probably many other ways
of doing this. Maybe some have yet to be discovered (by anyone).

HTH,
Ryan--

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


"JBeaucaire" wrote:

> This will check the whole column, but you can restrict it to just the range
> you've noted if you want.
>
> =SUM(IF(FREQUENCY(A:A,A:A)>0,1))
>
> --
> "Actually, I *am* a rocket scientist." -- JB
> (www.MadRocketScientist.com)
>
> Your feedback is appreciated, click YES if this post helped you.
>
>
> "Karen" wrote:
>
> > I have a range of cells (A5:A100). In some cells there are duplicate part
> > numbers. I want to count ONLY the unique values and not the duplicates. Does
> > anyone know how this can be done and what formula I would use to accomplish
> > this?
> > Thank you very much in advance, Karen