Hi,

If the range contained only numbers you could use this

=SUMPRODUCT(--(FREQUENCY(B1:B10,B1:B10)0))

Regarding how the two previous formulas work sustituting a smaller range:

COUNTIF(B1:B10,B1:B10&"") This portion of the formula counts how many time
each item in the range B1:B10 appears in the range B1:B10. It might return
something like this
{4;5;4;5;4;5;1;5;4;5}
If somethng is repeated 5 times there are 5 fives listed. If there are 5
fives then 1 unique items was found, if there were 10 fives that would mean 2
unique items had been found. If a 1 appears it means an item was only found
once. If there are 10 1's there were ten uniques items that appeared only
once.

The B1:B10<"" portion is just checking to see if the cells are not blank,
but it return something like
{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRU E}

Now when you divide (or add, subtract, multiply) a number into a TRUE Excel
converts the TRUE to 1, similarly a FALSE becomes 0. So in effect, think of
this as

{1;1;1;1;1;1;0;1;1;1}

Together this is

{1;1;1;1;1;1;0;1;1;1}/{4;5;4;5;4;5;1;5;4;5}

Which returns

{0.25;0.2;0.25;0.2;0.25;0.2;0;0.2;0.25;0.2}

Notice if an item appeared 5 times it carries a weight of .2, if it appeared
twice it has a weight of .5. If you add 5 .2's you get 1, if you add 2
..5's you get 1.
Sum this up and you have the number of unique items.

If this helps, please click the Yes button

Cheers,
Shane Devenshire

"PA" wrote:

I have a column with many duplicates. I need to get a count of each
individual record, ignoring the dupes so that
x
x
y
z
z
z
would yield a count of three. There are many more, spanning from G2:G9075.
Thanks
Paul