counta formula that doesn't count duplicates

[Meenie]

I have excel 2003
How can I create a formula that counts items in a column without counting
duplicates?
sorry if this comes through twice, I entered it once and it doesn't seem to
come up even though other new questions have popped in.
thanks Meenie

 

[Mike H]

Hi,

Try this with the range adjusted to suit

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

Mike

 

[Meenie]

thanks Mike, that works!

Hi,

Try this with the range adjusted to suit

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

Mike

 

[T. Valko]

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

You're asking for the wrath of Bernd! <g>

 

[Mike H]

Biff,

Not unusual for me these days, I'm being a bit slow!!

You're asking for the wrath of Bernd! <g>

Why?

Mike

 

[Mike H]

Ah,

I see what you mean Bernd seems to have reservations regarding that method.
Hope he doesn't spot my response ;(

http://www.sulprobil.com/html/count_unique.html

Mike

 

[T. Valko]

Yeah, that's it.

Seems every time I suggest a formula for counting uniques Bernd always finds
me and "chastizes" me for my suggestion.

 

[Bernd P]

Hello Biff, hello Mike,

Not today.

Have a Merry Christmas (tomorrow),
Bernd

 

[T. Valko]

Bernd always finds me

See what I mean? LOL

Merry Christmas, Bernd!

 

[Mike H]

Biff, Bernd,

On reflection I won't recommend that formula again I'll recommend the much
faster alternative. Goodnight both and a very happy Christmas.

Sincerely,

Mike