column variables

Try =CountA(C2:C65536) or something like that, mahmon.
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


"mahmon" <[email protected]> wrote in message
news:[email protected]...
> I have databse of products, one colum contains text values for the colours
> these products are, red, green , blue, etc. what functinocan tell me how
many
> colours are in the column? help please
>

On Mon, 27 Jun 2005 10:46:06 -0700, mahmon <[email protected]>
wrote:

>sorted! now that was much easier, thank you very much for all your help ron,
>yo are a legend of excel!
>
>wayne

You're welcome. I'm glad we got it working for you. Thank you for the
feedback.


--ron

sorted! now that was much easier, thank you very much for all your help ron,
yo are a legend of excel!

wayne

"Ron Rosenfeld" wrote:

> On Mon, 27 Jun 2005 05:36:07 -0700, mahmon <[email protected]>
> wrote:
>
> >i have nod discovered that the eror works inthis way:
> >
> >if there is a yes in one of each colour then the formula works correct,
> >otherwise it is 1 plus every time
>
> There is also a situation where you will get a count of 2 instead of 1.
>
> In any event, I will recommend an ultimately simpler approach for both
> problems. However, if you need to distribute the workbook to others, there may
> be an issue.
>
> Go to http://xcell05.free.fr/ and download and install Longre's free add-in:
> morefunc.xll
>
> Then use the following formulas:
>
> Number of color choices:
>
> =COUNTDIFF(colours)
>
> Number of color choices only in the selected products, use the *array* formula:
>
> =COUNTDIFF(IF(selected="yes",colours),,FALSE)
>
> (That last FALSE argument tells the COUNTDIFF function to NOT count the FALSE
> results).
>
>
> --ron
>

On Mon, 27 Jun 2005 05:36:07 -0700, mahmon <[email protected]>
wrote:

>i have nod discovered that the eror works inthis way:
>
>if there is a yes in one of each colour then the formula works correct,
>otherwise it is 1 plus every time

There is also a situation where you will get a count of 2 instead of 1.

In any event, I will recommend an ultimately simpler approach for both
problems. However, if you need to distribute the workbook to others, there may
be an issue.

Go to http://xcell05.free.fr/ and download and install Longre's free add-in:
morefunc.xll

Then use the following formulas:

Number of color choices:

=COUNTDIFF(colours)

Number of color choices only in the selected products, use the *array* formula:

=COUNTDIFF(IF(selected="yes",colours),,FALSE)

(That last FALSE argument tells the COUNTDIFF function to NOT count the FALSE
results).


--ron

On Mon, 27 Jun 2005 05:21:03 -0700, mahmon <[email protected]>
wrote:

>thanks again ron
>works good, only one problem, if there aren't any "yes" in the selected
>column the formula returns a value of 1 instead of 0? any ideas
>
>cheers
>
>As soon as you start ot input 'selected' t

Actually that's not true :-(( There are other instances where this second
formula is incorrect. Let me think about it for a bit.


--ron

i have nod discovered that the eror works inthis way:

if there is a yes in one of each colour then the formula works correct,
otherwise it is 1 plus every time

"Ron Rosenfeld" wrote:

> On Mon, 27 Jun 2005 01:29:01 -0700, mahmon <[email protected]>
> 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(colours,colours,0))>0,1))
>
> Number of color choices only in the selected products
>
> =SUM(IF(FREQUENCY(MATCH(colours,colours,0)*(selected="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
>

thanks again ron
works good, only one problem, if there aren't any "yes" in the selected
column the formula returns a value of 1 instead of 0? any ideas

cheers

As soon as you start ot input 'selected' the values are correct from 1 upwards

"Ron Rosenfeld" wrote:

> On Mon, 27 Jun 2005 01:29:01 -0700, mahmon <[email protected]>
> 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(colours,colours,0))>0,1))
>
> Number of color choices only in the selected products
>
> =SUM(IF(FREQUENCY(MATCH(colours,colours,0)*(selected="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
>

On Mon, 27 Jun 2005 01:29:01 -0700, mahmon <[email protected]>
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(colours,colours,0))>0,1))

Number of color choices only in the selected products

=SUM(IF(FREQUENCY(MATCH(colours,colours,0)*(selected="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

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




"Ron Rosenfeld" wrote:

> On Sun, 26 Jun 2005 12:04:02 -0700, mahmon <[email protected]>
> wrote:
>
> >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
>
> What exactly do you mean by "filters out"?
>
> Assuming you mean it the same way as Excel uses the term, with your cursor in
> the table, select Data/Filter/AutoFilter. Then click on the down arrow at the
> "Selected" column heading and choose something like "non-Blanks" or Does Not
> Equal Selected
>
> If you mean something else, please define it.
>
>
> --ron
>

On Sun, 26 Jun 2005 12:04:02 -0700, mahmon <[email protected]>
wrote:

>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

What exactly do you mean by "filters out"?

Assuming you mean it the same way as Excel uses the term, with your cursor in
the table, select Data/Filter/AutoFilter. Then click on the down arrow at the
"Selected" column heading and choose something like "non-Blanks" or Does Not
Equal Selected

If you mean something else, please define it.


--ron

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 <[email protected]>
> 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
>

On Sun, 26 Jun 2005 11:22:02 -0700, mahmon <[email protected]>
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

Do you want to count all the "Reds" that have "selected" in the adjoining
column?
Enter the color you're looking for in C1, and try this:

=SUMPRODUCT((A1:A1000=C1)*(B1:B1000="selected"))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"mahmon" <[email protected]> wrote in message
news:[email protected]...
> BRILLIANT IT WORKS! thanks for your help. 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' ?
>
> "Ron Rosenfeld" wrote:
>
> > On Sun, 26 Jun 2005 10:46:02 -0700, mahmon
<[email protected]>
> > wrote:
> >
> > >Thanks cant seem to get that to work dont know of its me, i am quite
new to
> > >excel. what do i put in place of rng? Wwhat i want to do is for excel
to look
> > >down the list and count the number of different colours without me
having to
> > >input any of the values in the function
> >
> > For "rng" you substitute the range which you are testing. So if your
data
> > (list of colors) is in C2:C1000, you would substitute C2:C1000 where I
have
> > written "rng".
> >
> > Alternatively, you could NAME that range, "rng" and then you would not
have to
> > substitute anything.
> >
> >
> > --ron
> >

rng is the range, mahmon. So if your colors are in A1:A10, then replace rng
with A1:A10.

Microsoft describes this solution here:
http://support.microsoft.com/kb/q268001/

*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


"mahmon" <[email protected]> wrote in message
news:[email protected]...
> Thanks cant seem to get that to work dont know of its me, i am quite new
to
> excel. what do i put in place of rng? Wwhat i want to do is for excel to
look
> down the list and count the number of different colours without me having
to
> input any of the values in the function
>
> "Ron Rosenfeld" wrote:
>
> > On Sun, 26 Jun 2005 08:55:04 -0700, mahmon
<[email protected]>
> > wrote:
> >
> > >I have databse of products, one colum contains text values for the
colours
> > >these products are, red, green , blue, etc. what functinocan tell me
how many
> > >colours are in the column? help please
> >
> > If there are no blanks in your range:
> >
> > =SUM(IF(FREQUENCY(MATCH(rng,rng,0),MATCH(rng,rng,0))>0,1))
> >
> > If there may be blanks, then use the *array* formula:
> >
> >
=SUM(IF(FREQUENCY(IF(LEN(rng)>0,MATCH(rng,rng,0),""),IF(LEN(rng)>0,MATCH(rng
,rng,0),""))>0,1))
> >
> > To enter an *array* formula, after typing or pasting it into the cell,
hold
> > down <ctrl><shift> while hitting <enter>.
> >
> >
> > --ron
> >

On Sun, 26 Jun 2005 10:46:02 -0700, mahmon <[email protected]>
wrote:

>Thanks cant seem to get that to work dont know of its me, i am quite new to
>excel. what do i put in place of rng? Wwhat i want to do is for excel to look
>down the list and count the number of different colours without me having to
>input any of the values in the function

For "rng" you substitute the range which you are testing. So if your data
(list of colors) is in C2:C1000, you would substitute C2:C1000 where I have
written "rng".

Alternatively, you could NAME that range, "rng" and then you would not have to
substitute anything.


--ron

thanks, but my problem is that the colours are repeated and i dont want it to
count a colour twice


"Anne Troy" wrote:

> Try =CountA(C2:C65536) or something like that, mahmon.
> *******************
> ~Anne Troy
>
> www.OfficeArticles.com
> www.MyExpertsOnline.com
>
>
> "mahmon" <[email protected]> wrote in message
> news:[email protected]...
> > I have databse of products, one colum contains text values for the colours
> > these products are, red, green , blue, etc. what functinocan tell me how
> many
> > colours are in the column? help please
> >
>
>
>

On Sun, 26 Jun 2005 08:55:04 -0700, mahmon <[email protected]>
wrote:

>I have databse of products, one colum contains text values for the colours
>these products are, red, green , blue, etc. what functinocan tell me how many
>colours are in the column? help please

If there are no blanks in your range:

=SUM(IF(FREQUENCY(MATCH(rng,rng,0),MATCH(rng,rng,0))>0,1))

If there may be blanks, then use the *array* formula:

=SUM(IF(FREQUENCY(IF(LEN(rng)>0,MATCH(rng,rng,0),""),IF(LEN(rng)>0,MATCH(rng,rng,0),""))>0,1))

To enter an *array* formula, after typing or pasting it into the cell, hold
down <ctrl><shift> while hitting <enter>.


--ron

BRILLIANT IT WORKS! thanks for your help. 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' ?

"Ron Rosenfeld" wrote:

> On Sun, 26 Jun 2005 10:46:02 -0700, mahmon <[email protected]>
> wrote:
>
> >Thanks cant seem to get that to work dont know of its me, i am quite new to
> >excel. what do i put in place of rng? Wwhat i want to do is for excel to look
> >down the list and count the number of different colours without me having to
> >input any of the values in the function
>
> For "rng" you substitute the range which you are testing. So if your data
> (list of colors) is in C2:C1000, you would substitute C2:C1000 where I have
> written "rng".
>
> Alternatively, you could NAME that range, "rng" and then you would not have to
> substitute anything.
>
>
> --ron
>

Thanks cant seem to get that to work dont know of its me, i am quite new to
excel. what do i put in place of rng? Wwhat i want to do is for excel to look
down the list and count the number of different colours without me having to
input any of the values in the function

"Ron Rosenfeld" wrote:

> On Sun, 26 Jun 2005 08:55:04 -0700, mahmon <[email protected]>
> wrote:
>
> >I have databse of products, one colum contains text values for the colours
> >these products are, red, green , blue, etc. what functinocan tell me how many
> >colours are in the column? help please
>
> If there are no blanks in your range:
>
> =SUM(IF(FREQUENCY(MATCH(rng,rng,0),MATCH(rng,rng,0))>0,1))
>
> If there may be blanks, then use the *array* formula:
>
> =SUM(IF(FREQUENCY(IF(LEN(rng)>0,MATCH(rng,rng,0),""),IF(LEN(rng)>0,MATCH(rng,rng,0),""))>0,1))
>
> To enter an *array* formula, after typing or pasting it into the cell, hold
> down <ctrl><shift> while hitting <enter>.
>
>
> --ron
>

rng is the range, mahmon. So if your colors are in A1:A10, then replace rng
with A1:A10.

Microsoft describes this solution here:
http://support.microsoft.com/kb/q268001/

*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


"mahmon" <[email protected]> wrote in message
news:[email protected]...
> Thanks cant seem to get that to work dont know of its me, i am quite new
to
> excel. what do i put in place of rng? Wwhat i want to do is for excel to
look
> down the list and count the number of different colours without me having
to
> input any of the values in the function
>
> "Ron Rosenfeld" wrote:
>
> > On Sun, 26 Jun 2005 08:55:04 -0700, mahmon
<[email protected]>
> > wrote:
> >
> > >I have databse of products, one colum contains text values for the
colours
> > >these products are, red, green , blue, etc. what functinocan tell me
how many
> > >colours are in the column? help please
> >
> > If there are no blanks in your range:
> >
> > =SUM(IF(FREQUENCY(MATCH(rng,rng,0),MATCH(rng,rng,0))>0,1))
> >
> > If there may be blanks, then use the *array* formula:
> >
> >
=SUM(IF(FREQUENCY(IF(LEN(rng)>0,MATCH(rng,rng,0),""),IF(LEN(rng)>0,MATCH(rng
,rng,0),""))>0,1))
> >
> > To enter an *array* formula, after typing or pasting it into the cell,
hold
> > down <ctrl><shift> while hitting <enter>.
> >
> >
> > --ron
> >

On Mon, 27 Jun 2005 10:46:06 -0700, mahmon <[email protected]>
wrote:

>sorted! now that was much easier, thank you very much for all your help ron,
>yo are a legend of excel!
>
>wayne

You're welcome. I'm glad we got it working for you. Thank you for the
feedback.


--ron

Try =CountA(C2:C65536) or something like that, mahmon.
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


"mahmon" <[email protected]> wrote in message
news:[email protected]...
> I have databse of products, one colum contains text values for the colours
> these products are, red, green , blue, etc. what functinocan tell me how
many
> colours are in the column? help please
>

sorted! now that was much easier, thank you very much for all your help ron,
yo are a legend of excel!

wayne

"Ron Rosenfeld" wrote:

> On Mon, 27 Jun 2005 05:36:07 -0700, mahmon <[email protected]>
> wrote:
>
> >i have nod discovered that the eror works inthis way:
> >
> >if there is a yes in one of each colour then the formula works correct,
> >otherwise it is 1 plus every time
>
> There is also a situation where you will get a count of 2 instead of 1.
>
> In any event, I will recommend an ultimately simpler approach for both
> problems. However, if you need to distribute the workbook to others, there may
> be an issue.
>
> Go to http://xcell05.free.fr/ and download and install Longre's free add-in:
> morefunc.xll
>
> Then use the following formulas:
>
> Number of color choices:
>
> =COUNTDIFF(colours)
>
> Number of color choices only in the selected products, use the *array* formula:
>
> =COUNTDIFF(IF(selected="yes",colours),,FALSE)
>
> (That last FALSE argument tells the COUNTDIFF function to NOT count the FALSE
> results).
>
>
> --ron
>

thanks, but my problem is that the colours are repeated and i dont want it to
count a colour twice


"Anne Troy" wrote:

> Try =CountA(C2:C65536) or something like that, mahmon.
> *******************
> ~Anne Troy
>
> www.OfficeArticles.com
> www.MyExpertsOnline.com
>
>
> "mahmon" <[email protected]> wrote in message
> news:[email protected]...
> > I have databse of products, one colum contains text values for the colours
> > these products are, red, green , blue, etc. what functinocan tell me how
> many
> > colours are in the column? help please
> >
>
>
>

On Mon, 27 Jun 2005 05:36:07 -0700, mahmon <[email protected]>
wrote:

>i have nod discovered that the eror works inthis way:
>
>if there is a yes in one of each colour then the formula works correct,
>otherwise it is 1 plus every time

There is also a situation where you will get a count of 2 instead of 1.

In any event, I will recommend an ultimately simpler approach for both
problems. However, if you need to distribute the workbook to others, there may
be an issue.

Go to http://xcell05.free.fr/ and download and install Longre's free add-in:
morefunc.xll

Then use the following formulas:

Number of color choices:

=COUNTDIFF(colours)

Number of color choices only in the selected products, use the *array* formula:

=COUNTDIFF(IF(selected="yes",colours),,FALSE)

(That last FALSE argument tells the COUNTDIFF function to NOT count the FALSE
results).


--ron

On Sun, 26 Jun 2005 08:55:04 -0700, mahmon <[email protected]>
wrote:

>I have databse of products, one colum contains text values for the colours
>these products are, red, green , blue, etc. what functinocan tell me how many
>colours are in the column? help please

If there are no blanks in your range:

=SUM(IF(FREQUENCY(MATCH(rng,rng,0),MATCH(rng,rng,0))>0,1))

If there may be blanks, then use the *array* formula:

=SUM(IF(FREQUENCY(IF(LEN(rng)>0,MATCH(rng,rng,0),""),IF(LEN(rng)>0,MATCH(rng,rng,0),""))>0,1))

To enter an *array* formula, after typing or pasting it into the cell, hold
down <ctrl><shift> while hitting <enter>.


--ron

Thanks cant seem to get that to work dont know of its me, i am quite new to
excel. what do i put in place of rng? Wwhat i want to do is for excel to look
down the list and count the number of different colours without me having to
input any of the values in the function

"Ron Rosenfeld" wrote:

> On Sun, 26 Jun 2005 08:55:04 -0700, mahmon <[email protected]>
> wrote:
>
> >I have databse of products, one colum contains text values for the colours
> >these products are, red, green , blue, etc. what functinocan tell me how many
> >colours are in the column? help please
>
> If there are no blanks in your range:
>
> =SUM(IF(FREQUENCY(MATCH(rng,rng,0),MATCH(rng,rng,0))>0,1))
>
> If there may be blanks, then use the *array* formula:
>
> =SUM(IF(FREQUENCY(IF(LEN(rng)>0,MATCH(rng,rng,0),""),IF(LEN(rng)>0,MATCH(rng,rng,0),""))>0,1))
>
> To enter an *array* formula, after typing or pasting it into the cell, hold
> down <ctrl><shift> while hitting <enter>.
>
>
> --ron
>

On Mon, 27 Jun 2005 05:21:03 -0700, mahmon <[email protected]>
wrote:

>thanks again ron
>works good, only one problem, if there aren't any "yes" in the selected
>column the formula returns a value of 1 instead of 0? any ideas
>
>cheers
>
>As soon as you start ot input 'selected' t

Actually that's not true :-(( There are other instances where this second
formula is incorrect. Let me think about it for a bit.


--ron

On Sun, 26 Jun 2005 10:46:02 -0700, mahmon <[email protected]>
wrote:

>Thanks cant seem to get that to work dont know of its me, i am quite new to
>excel. what do i put in place of rng? Wwhat i want to do is for excel to look
>down the list and count the number of different colours without me having to
>input any of the values in the function

For "rng" you substitute the range which you are testing. So if your data
(list of colors) is in C2:C1000, you would substitute C2:C1000 where I have
written "rng".

Alternatively, you could NAME that range, "rng" and then you would not have to
substitute anything.


--ron

i have nod discovered that the eror works inthis way:

if there is a yes in one of each colour then the formula works correct,
otherwise it is 1 plus every time

"Ron Rosenfeld" wrote:

> On Mon, 27 Jun 2005 01:29:01 -0700, mahmon <[email protected]>
> 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(colours,colours,0))>0,1))
>
> Number of color choices only in the selected products
>
> =SUM(IF(FREQUENCY(MATCH(colours,colours,0)*(selected="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
>

BRILLIANT IT WORKS! thanks for your help. 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' ?

"Ron Rosenfeld" wrote:

> On Sun, 26 Jun 2005 10:46:02 -0700, mahmon <[email protected]>
> wrote:
>
> >Thanks cant seem to get that to work dont know of its me, i am quite new to
> >excel. what do i put in place of rng? Wwhat i want to do is for excel to look
> >down the list and count the number of different colours without me having to
> >input any of the values in the function
>
> For "rng" you substitute the range which you are testing. So if your data
> (list of colors) is in C2:C1000, you would substitute C2:C1000 where I have
> written "rng".
>
> Alternatively, you could NAME that range, "rng" and then you would not have to
> substitute anything.
>
>
> --ron
>

Do you want to count all the "Reds" that have "selected" in the adjoining
column?
Enter the color you're looking for in C1, and try this:

=SUMPRODUCT((A1:A1000=C1)*(B1:B1000="selected"))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"mahmon" <[email protected]> wrote in message
news:[email protected]...
> BRILLIANT IT WORKS! thanks for your help. 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' ?
>
> "Ron Rosenfeld" wrote:
>
> > On Sun, 26 Jun 2005 10:46:02 -0700, mahmon
<[email protected]>
> > wrote:
> >
> > >Thanks cant seem to get that to work dont know of its me, i am quite
new to
> > >excel. what do i put in place of rng? Wwhat i want to do is for excel
to look
> > >down the list and count the number of different colours without me
having to
> > >input any of the values in the function
> >
> > For "rng" you substitute the range which you are testing. So if your
data
> > (list of colors) is in C2:C1000, you would substitute C2:C1000 where I
have
> > written "rng".
> >
> > Alternatively, you could NAME that range, "rng" and then you would not
have to
> > substitute anything.
> >
> >
> > --ron
> >

thanks again ron
works good, only one problem, if there aren't any "yes" in the selected
column the formula returns a value of 1 instead of 0? any ideas

cheers

As soon as you start ot input 'selected' the values are correct from 1 upwards

"Ron Rosenfeld" wrote:

> On Mon, 27 Jun 2005 01:29:01 -0700, mahmon <[email protected]>
> 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(colours,colours,0))>0,1))
>
> Number of color choices only in the selected products
>
> =SUM(IF(FREQUENCY(MATCH(colours,colours,0)*(selected="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
>

On Sun, 26 Jun 2005 11:22:02 -0700, mahmon <[email protected]>
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

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 <[email protected]>
> 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
>

On Mon, 27 Jun 2005 01:29:01 -0700, mahmon <[email protected]>
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(colours,colours,0))>0,1))

Number of color choices only in the selected products

=SUM(IF(FREQUENCY(MATCH(colours,colours,0)*(selected="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

On Sun, 26 Jun 2005 12:04:02 -0700, mahmon <[email protected]>
wrote:

>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

What exactly do you mean by "filters out"?

Assuming you mean it the same way as Excel uses the term, with your cursor in
the table, select Data/Filter/AutoFilter. Then click on the down arrow at the
"Selected" column heading and choose something like "non-Blanks" or Does Not
Equal Selected

If you mean something else, please define it.


--ron

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




"Ron Rosenfeld" wrote:

> On Sun, 26 Jun 2005 12:04:02 -0700, mahmon <[email protected]>
> wrote:
>
> >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
>
> What exactly do you mean by "filters out"?
>
> Assuming you mean it the same way as Excel uses the term, with your cursor in
> the table, select Data/Filter/AutoFilter. Then click on the down arrow at the
> "Selected" column heading and choose something like "non-Blanks" or Does Not
> Equal Selected
>
> If you mean something else, please define it.
>
>
> --ron
>

On Sun, 26 Jun 2005 11:22:02 -0700, mahmon <[email protected]>
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

I have databse of products, one colum contains text values for the colours
these products are, red, green , blue, etc. what functinocan tell me how many
colours are in the column? help please

Try =CountA(C2:C65536) or something like that, mahmon.
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


"mahmon" <[email protected]> wrote in message
news:[email protected]...
> I have databse of products, one colum contains text values for the colours
> these products are, red, green , blue, etc. what functinocan tell me how
many
> colours are in the column? help please
>

thanks, but my problem is that the colours are repeated and i dont want it to
count a colour twice


"Anne Troy" wrote:

> Try =CountA(C2:C65536) or something like that, mahmon.
> *******************
> ~Anne Troy
>
> www.OfficeArticles.com
> www.MyExpertsOnline.com
>
>
> "mahmon" <[email protected]> wrote in message
> news:[email protected]...
> > I have databse of products, one colum contains text values for the colours
> > these products are, red, green , blue, etc. what functinocan tell me how
> many
> > colours are in the column? help please
> >
>
>
>

On Sun, 26 Jun 2005 08:55:04 -0700, mahmon <[email protected]>
wrote:

>I have databse of products, one colum contains text values for the colours
>these products are, red, green , blue, etc. what functinocan tell me how many
>colours are in the column? help please

If there are no blanks in your range:

=SUM(IF(FREQUENCY(MATCH(rng,rng,0),MATCH(rng,rng,0))>0,1))

If there may be blanks, then use the *array* formula:

=SUM(IF(FREQUENCY(IF(LEN(rng)>0,MATCH(rng,rng,0),""),IF(LEN(rng)>0,MATCH(rng,rng,0),""))>0,1))

To enter an *array* formula, after typing or pasting it into the cell, hold
down <ctrl><shift> while hitting <enter>.


--ron

Thanks cant seem to get that to work dont know of its me, i am quite new to
excel. what do i put in place of rng? Wwhat i want to do is for excel to look
down the list and count the number of different colours without me having to
input any of the values in the function

"Ron Rosenfeld" wrote:

> On Sun, 26 Jun 2005 08:55:04 -0700, mahmon <[email protected]>
> wrote:
>
> >I have databse of products, one colum contains text values for the colours
> >these products are, red, green , blue, etc. what functinocan tell me how many
> >colours are in the column? help please
>
> If there are no blanks in your range:
>
> =SUM(IF(FREQUENCY(MATCH(rng,rng,0),MATCH(rng,rng,0))>0,1))
>
> If there may be blanks, then use the *array* formula:
>
> =SUM(IF(FREQUENCY(IF(LEN(rng)>0,MATCH(rng,rng,0),""),IF(LEN(rng)>0,MATCH(rng,rng,0),""))>0,1))
>
> To enter an *array* formula, after typing or pasting it into the cell, hold
> down <ctrl><shift> while hitting <enter>.
>
>
> --ron
>

On Sun, 26 Jun 2005 10:46:02 -0700, mahmon <[email protected]>
wrote:

>Thanks cant seem to get that to work dont know of its me, i am quite new to
>excel. what do i put in place of rng? Wwhat i want to do is for excel to look
>down the list and count the number of different colours without me having to
>input any of the values in the function

For "rng" you substitute the range which you are testing. So if your data
(list of colors) is in C2:C1000, you would substitute C2:C1000 where I have
written "rng".

Alternatively, you could NAME that range, "rng" and then you would not have to
substitute anything.


--ron

rng is the range, mahmon. So if your colors are in A1:A10, then replace rng
with A1:A10.

Microsoft describes this solution here:
http://support.microsoft.com/kb/q268001/

*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


"mahmon" <[email protected]> wrote in message
news:[email protected]...
> Thanks cant seem to get that to work dont know of its me, i am quite new
to
> excel. what do i put in place of rng? Wwhat i want to do is for excel to
look
> down the list and count the number of different colours without me having
to
> input any of the values in the function
>
> "Ron Rosenfeld" wrote:
>
> > On Sun, 26 Jun 2005 08:55:04 -0700, mahmon
<[email protected]>
> > wrote:
> >
> > >I have databse of products, one colum contains text values for the
colours
> > >these products are, red, green , blue, etc. what functinocan tell me
how many
> > >colours are in the column? help please
> >
> > If there are no blanks in your range:
> >
> > =SUM(IF(FREQUENCY(MATCH(rng,rng,0),MATCH(rng,rng,0))>0,1))
> >
> > If there may be blanks, then use the *array* formula:
> >
> >
=SUM(IF(FREQUENCY(IF(LEN(rng)>0,MATCH(rng,rng,0),""),IF(LEN(rng)>0,MATCH(rng
,rng,0),""))>0,1))
> >
> > To enter an *array* formula, after typing or pasting it into the cell,
hold
> > down <ctrl><shift> while hitting <enter>.
> >
> >
> > --ron
> >

BRILLIANT IT WORKS! thanks for your help. 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' ?

"Ron Rosenfeld" wrote:

> On Sun, 26 Jun 2005 10:46:02 -0700, mahmon <[email protected]>
> wrote:
>
> >Thanks cant seem to get that to work dont know of its me, i am quite new to
> >excel. what do i put in place of rng? Wwhat i want to do is for excel to look
> >down the list and count the number of different colours without me having to
> >input any of the values in the function
>
> For "rng" you substitute the range which you are testing. So if your data
> (list of colors) is in C2:C1000, you would substitute C2:C1000 where I have
> written "rng".
>
> Alternatively, you could NAME that range, "rng" and then you would not have to
> substitute anything.
>
>
> --ron
>

Do you want to count all the "Reds" that have "selected" in the adjoining
column?
Enter the color you're looking for in C1, and try this:

=SUMPRODUCT((A1:A1000=C1)*(B1:B1000="selected"))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"mahmon" <[email protected]> wrote in message
news:[email protected]...
> BRILLIANT IT WORKS! thanks for your help. 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' ?
>
> "Ron Rosenfeld" wrote:
>
> > On Sun, 26 Jun 2005 10:46:02 -0700, mahmon
<[email protected]>
> > wrote:
> >
> > >Thanks cant seem to get that to work dont know of its me, i am quite
new to
> > >excel. what do i put in place of rng? Wwhat i want to do is for excel
to look
> > >down the list and count the number of different colours without me
having to
> > >input any of the values in the function
> >
> > For "rng" you substitute the range which you are testing. So if your
data
> > (list of colors) is in C2:C1000, you would substitute C2:C1000 where I
have
> > written "rng".
> >
> > Alternatively, you could NAME that range, "rng" and then you would not
have to
> > substitute anything.
> >
> >
> > --ron
> >

On Mon, 27 Jun 2005 10:46:06 -0700, mahmon <[email protected]>
wrote:

>sorted! now that was much easier, thank you very much for all your help ron,
>yo are a legend of excel!
>
>wayne

You're welcome. I'm glad we got it working for you. Thank you for the
feedback.


--ron

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 <[email protected]>
> 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
>

On Sun, 26 Jun 2005 12:04:02 -0700, mahmon <[email protected]>
wrote:

>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

What exactly do you mean by "filters out"?

Assuming you mean it the same way as Excel uses the term, with your cursor in
the table, select Data/Filter/AutoFilter. Then click on the down arrow at the
"Selected" column heading and choose something like "non-Blanks" or Does Not
Equal Selected

If you mean something else, please define it.


--ron

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




"Ron Rosenfeld" wrote:

> On Sun, 26 Jun 2005 12:04:02 -0700, mahmon <[email protected]>
> wrote:
>
> >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
>
> What exactly do you mean by "filters out"?
>
> Assuming you mean it the same way as Excel uses the term, with your cursor in
> the table, select Data/Filter/AutoFilter. Then click on the down arrow at the
> "Selected" column heading and choose something like "non-Blanks" or Does Not
> Equal Selected
>
> If you mean something else, please define it.
>
>
> --ron
>

On Mon, 27 Jun 2005 01:29:01 -0700, mahmon <[email protected]>
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(colours,colours,0))>0,1))

Number of color choices only in the selected products

=SUM(IF(FREQUENCY(MATCH(colours,colours,0)*(selected="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

thanks again ron
works good, only one problem, if there aren't any "yes" in the selected
column the formula returns a value of 1 instead of 0? any ideas

cheers

As soon as you start ot input 'selected' the values are correct from 1 upwards

"Ron Rosenfeld" wrote:

> On Mon, 27 Jun 2005 01:29:01 -0700, mahmon <[email protected]>
> 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(colours,colours,0))>0,1))
>
> Number of color choices only in the selected products
>
> =SUM(IF(FREQUENCY(MATCH(colours,colours,0)*(selected="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
>

i have nod discovered that the eror works inthis way:

if there is a yes in one of each colour then the formula works correct,
otherwise it is 1 plus every time

"Ron Rosenfeld" wrote:

> On Mon, 27 Jun 2005 01:29:01 -0700, mahmon <[email protected]>
> 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(colours,colours,0))>0,1))
>
> Number of color choices only in the selected products
>
> =SUM(IF(FREQUENCY(MATCH(colours,colours,0)*(selected="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
>

On Mon, 27 Jun 2005 05:21:03 -0700, mahmon <[email protected]>
wrote:

>thanks again ron
>works good, only one problem, if there aren't any "yes" in the selected
>column the formula returns a value of 1 instead of 0? any ideas
>
>cheers
>
>As soon as you start ot input 'selected' t

Actually that's not true :-(( There are other instances where this second
formula is incorrect. Let me think about it for a bit.


--ron

On Mon, 27 Jun 2005 05:36:07 -0700, mahmon <[email protected]>
wrote:

>i have nod discovered that the eror works inthis way:
>
>if there is a yes in one of each colour then the formula works correct,
>otherwise it is 1 plus every time

There is also a situation where you will get a count of 2 instead of 1.

In any event, I will recommend an ultimately simpler approach for both
problems. However, if you need to distribute the workbook to others, there may
be an issue.

Go to http://xcell05.free.fr/ and download and install Longre's free add-in:
morefunc.xll

Then use the following formulas:

Number of color choices:

=COUNTDIFF(colours)

Number of color choices only in the selected products, use the *array* formula:

=COUNTDIFF(IF(selected="yes",colours),,FALSE)

(That last FALSE argument tells the COUNTDIFF function to NOT count the FALSE
results).


--ron

sorted! now that was much easier, thank you very much for all your help ron,
yo are a legend of excel!

wayne

"Ron Rosenfeld" wrote:

> On Mon, 27 Jun 2005 05:36:07 -0700, mahmon <[email protected]>
> wrote:
>
> >i have nod discovered that the eror works inthis way:
> >
> >if there is a yes in one of each colour then the formula works correct,
> >otherwise it is 1 plus every time
>
> There is also a situation where you will get a count of 2 instead of 1.
>
> In any event, I will recommend an ultimately simpler approach for both
> problems. However, if you need to distribute the workbook to others, there may
> be an issue.
>
> Go to http://xcell05.free.fr/ and download and install Longre's free add-in:
> morefunc.xll
>
> Then use the following formulas:
>
> Number of color choices:
>
> =COUNTDIFF(colours)
>
> Number of color choices only in the selected products, use the *array* formula:
>
> =COUNTDIFF(IF(selected="yes",colours),,FALSE)
>
> (That last FALSE argument tells the COUNTDIFF function to NOT count the FALSE
> results).
>
>
> --ron
>