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;TRUE}
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
