Count If formula

[Guest]

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?

 

[Bob Phillips]

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.

 

[Guest]

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

 

[Bob Phillips]

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

 

[Guest]

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.

 

[Don Guillett]

Make your own

Sub makecolortable()
For i = 1 To 56
Cells(i, 1) = i
Cells(i, 2).Interior.ColorIndex = i
Next i
End Sub

 

[Guest]

Thats great Thanks for your help guys!

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