Count If formula

J

Jo Davis

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

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

Duke Carey

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

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
 
J

Jo Davis

Thats great Thanks for your help guys!

Bob Phillips said:
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))
Click to expand...
 
Top