Count Distinct

[dragonmouse]

I have a spreadsheet that may have multiple numbers for the same item...I want to simply count how many are in the row that are UNIQUE

Example

A B
1 Power supply 1111
2 Mother board 1111
3 Circuit card 1111
4 Power Supply 2222
5 Power supply 3333
6 Cirucit card 3333

If I =counta(B1:B6) it will return 6...The answer I want it to return is "3" because there are only really 3 "distinct" or "unique" numbers

Thanks

 

[dragonmouse]

Oh and by the way a Pivot Table really won't help me in this case.

Thanks,

 

[Jonmo1]

Try

=SUMPRODUCT(1/COUNTIF(B1:B6,B1:B6))

Hope that helps.

 

[dragonmouse]

Absolutely AMAZING. That looks like it did it.

 

[Jonmo1]

Glad to help, Thanks for the feedback