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" wrote in message
...
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" wrote in message
...
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
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
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
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

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
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" wrote in message
...
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

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 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

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

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

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

www.OfficeArticles.com
www.MyExpertsOnline.com


"mahmon" wrote in message
...
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

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