not quite what i wanted to achieve, i've used your formula

=SUM(IF(FREQUENCY(IF(LEN(D17:D32)0,MATCH(D17:D32, D17:D32,0),""),IF(LEN(D17:D32)0,MATCH(D17:D32,D17 :D32,0),""))0,1))

to count the number of options in the column, what i want to do now is add
to this forula so that it filters out all the ones that dont have 'selected'
in an adjacent coloumn. but i dont want to define a colour of interest

"Ron Rosenfeld" wrote:

On Sun, 26 Jun 2005 11:22:02 -0700, mahmon
wrote:

BRILLIANT IT WORKS! thanks for your help.

You're welcome. Thank you for the feedback.



one more question, how do then
cross reference to values in another column? i have a second coloumn that has
either a blank of the word 'selected' in it. how do i then count all the same
criteria but only if they are 'selected' ?

If your Colors are in a range named "Colors" (or use the appropriate cell
reference); and your column that may or may not have "Selected" in it is in a
range named "Sel" then

with the color of interest in A1:

=SUMPRODUCT((A1=Colors)*(Sel="Selected"))


--ron