On Mon, 27 Jun 2005 01:29:01 -0700, mahmon
wrote:

Dear Ron

okay here goes:

below is a simple version of what i want to achieve,
I have the formula now for the first one, but its the second one that i am
struggling with. i want to be able to enter data in the table as it grows and
the two formulas at the top update as you go. sorry if i was not clearer
before, and thanks for all your help so far

number of colour choices = 3 (this was your first formula, thankyou!)
number of colour choices of selected products = 2 (? how do we do this?)

part no colours selected
1 red no
2 red yes
3 blue no
4 green no
5 green yes





OK that is more clear.

If there are no blank colors in your list, then:

Number of color choices:

=SUM(IF(FREQUENCY(MATCH(colours,colours,0),MATCH(c olours,colours,0))0,1))

Number of color choices only in the selected products

=SUM(IF(FREQUENCY(MATCH(colours,colours,0)*(select ed="yes"),MATCH(colours,colours,0))0,1))

If there could be blank choices in the list of colors, then (as
array-formulas):

Number of color choices:
=SUM(IF(FREQUENCY(IF(LEN(colours)0,MATCH(colours, colours,0),""),
IF(LEN(colours)0,MATCH(colours,colours,0),""))0, 1))

Number of color choices only in the selected products:
=SUM(IF(FREQUENCY(IF(LEN(colours)0,MATCH(colours, colours,0)*(selected="yes"),""),
IF(LEN(colours)0,MATCH(colours,colours,0),""))0, 1))


--ron