Count If formula
 

Hi

Can you use the count if formula to say:

count how many cells say "sold" in blue text and how many say it in green
text?

You need a function to coun t the colour, like the one at
http://www.xldynamic.com/source/xld.ColourCounter.html

and then combine this with the text test like so,

=SUMPRODUCT(--(A1:A100="sold"),--(ColorIndex(A1:A100)=3))

to test for red solds.


--
HTH

Bob Phillips

"Jo Davis" wrote in message
...
Hi

Can you use the count if formula to say:

count how many cells say "sold" in blue text and how many say it in green
text?

Thanks Bob, thats great, how do you know what the numbers are for each colour

"Bob Phillips" wrote:

You need a function to coun t the colour, like the one at
http://www.xldynamic.com/source/xld.ColourCounter.html

and then combine this with the text test like so,

=SUMPRODUCT(--(A1:A100="sold"),--(ColorIndex(A1:A100)=3))

to test for red solds.


--
HTH

Bob Phillips

"Jo Davis" wrote in message
...
Hi

Can you use the count if formula to say:

count how many cells say "sold" in blue text and how many say it in green
text?

That is the tricky bit Jo. In a normal workbook, you can look up the values
in the help or run this bit of code to list them on a worksheet

Dim i As Long
For i = 1 To 56
Cells(i, "A") = i
Cells(i, "B").Interior.ColorIndex = i
Next i

Remember that these colours do not have a name within Excel, as they are
configurable. AN other way is to put the colour that you want to test in a
cell, say H1, and use that in the formula

=SUMPRODUCT(--(A1:A100="sold"),--(ColorIndex(A1:A100)=H1))

--
HTH

Bob Phillips

"Jo Davis" wrote in message
...
Thanks Bob, thats great, how do you know what the numbers are for each
colour

"Bob Phillips" wrote:

You need a function to coun t the colour, like the one at
http://www.xldynamic.com/source/xld.ColourCounter.html

and then combine this with the text test like so,

=SUMPRODUCT(--(A1:A100="sold"),--(ColorIndex(A1:A100)=3))

to test for red solds.


--
HTH

Bob Phillips

"Jo Davis" wrote in message
...
Hi

Can you use the count if formula to say:

count how many cells say "sold" in blue text and how many say it in
green
text?

To get the Color Index for the cells you want to count:

1) Select a cell with blue text
2) Press Alt-F11 to open the VBE
3) press Ctrl-G to open the Immediate Window
4) type in - ?Activecell.Font.ColorIndex
5) press the Enter key

at that point Excel will report the color index of that font. Repeat the
process for your green-colored font.

"Jo Davis" wrote:

Thanks Bob, thats great, how do you know what the numbers are for each colour

"Bob Phillips" wrote:

You need a function to coun t the colour, like the one at
http://www.xldynamic.com/source/xld.ColourCounter.html

and then combine this with the text test like so,

=SUMPRODUCT(--(A1:A100="sold"),--(ColorIndex(A1:A100)=3))

to test for red solds.


--
HTH

Bob Phillips

"Jo Davis" wrote in message
...
Hi

Can you use the count if formula to say:

count how many cells say "sold" in blue text and how many say it in green
text?

Make your own

Sub makecolortable()
For i = 1 To 56
Cells(i, 1) = i
Cells(i, 2).Interior.ColorIndex = i
Next i
End Sub
--
Don Guillett
SalesAid Software


"Jo Davis" wrote in message
...
Thanks Bob, thats great, how do you know what the numbers are for each
colour

"Bob Phillips" wrote:

You need a function to coun t the colour, like the one at
http://www.xldynamic.com/source/xld.ColourCounter.html

and then combine this with the text test like so,

=SUMPRODUCT(--(A1:A100="sold"),--(ColorIndex(A1:A100)=3))

to test for red solds.


--
HTH

Bob Phillips

"Jo Davis" wrote in message
...
Hi

Can you use the count if formula to say:

count how many cells say "sold" in blue text and how many say it in
green
text?

Thats great Thanks for your help guys!

"Bob Phillips" wrote:

That is the tricky bit Jo. In a normal workbook, you can look up the values
in the help or run this bit of code to list them on a worksheet

Dim i As Long
For i = 1 To 56
Cells(i, "A") = i
Cells(i, "B").Interior.ColorIndex = i
Next i

Remember that these colours do not have a name within Excel, as they are
configurable. AN other way is to put the colour that you want to test in a
cell, say H1, and use that in the formula

=SUMPRODUCT(--(A1:A100="sold"),--(ColorIndex(A1:A100)=H1))

--
HTH

Bob Phillips

"Jo Davis" wrote in message
...
Thanks Bob, thats great, how do you know what the numbers are for each
colour

"Bob Phillips" wrote:

You need a function to coun t the colour, like the one at
http://www.xldynamic.com/source/xld.ColourCounter.html

and then combine this with the text test like so,

=SUMPRODUCT(--(A1:A100="sold"),--(ColorIndex(A1:A100)=3))

to test for red solds.


--
HTH

Bob Phillips

"Jo Davis" wrote in message
...
Hi

Can you use the count if formula to say:

count how many cells say "sold" in blue text and how many say it in
green
text?