Counting UNIQUE occurrences within a range

[lindasf]

I need to display a count of how many UNIQUE values appear in a range
For example:

AM3:AM11 would show a count of 3. The result would be displayed in cel
AM117.

AN3:AN11 would show a count of 2. The result would be displayed in cel
AN117.


Unfortunately, the range cells won’t be in any particular sort orde
and some cells will have blanks.

I don’t know VBA, but I suspect that this must be done with a macr
(which I don’t know how to write), as I can’t find any formula to d
this...


Attached is a sample spreadsheet.

TI

Attachment filename: calwin training class schedule 062404-demo.xl
Download attachment: http://www.excelforum.com/attachment.php?postid=59261

 

[Peo Sjoblom]

One way

=SUMPRODUCT((AM3:AM11<>"")/(COUNTIF(AM3:AM11,AM3:AM11)+(AM3:AM11="")))


another

=SUM(IF(AM3:AM11<>0,1/COUNTIF(AM3:AM11,AM3:AM11)))

the second is shorter but needs to be entered with ctrl + shift & enter

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[kraljb]

If it is as it is in your spreadsheet with a limited number of option
for it to be unique, you could use the following:

=if(countif(am3:am17,"cpe2")<1,0,1)+if(countif(am3:am17,"cpe3")<1,0,1)+if(countif(am3:am17,"cpe1")<1,0,1)

im sure there has to be a better way..

 

[Harlan Grove]

One way

=SUMPRODUCT((AM3:AM11<>"")/(COUNTIF(AM3:AM11,AM3:AM11)+(AM3:AM11="")))

....

This form is safe if some cells in AM3:AM11 might be outside their
worksheet's used range. If that's not an issue, this could be shortened to

=SUMPRODUCT((AM3:AM11<>"")/COUNTIF(AM3:AM11,AM3:AM11&""))