how to make excel simply count red x's?

[M.C.McFockedup]

I just need excel to simply count the red x's in my document. It will
simplify all kinds of work. I have tried:
=SUMPRODUCT(--(ColorIndex(A1:A100,TRUE)=3)) , but i keep getting a name
error. When i change SUMPRODUCT to COUNT i dont get the name error but
it shows 0. Are there any simple solutions to this? I dont have the
time to get into visual basic either.

 

[Bob Phillips]

That is because you need a ColorIndex function, it doesn't come packaged
with Excel. Go back to the website you got it from, and copy that function
into your workbook.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Greg Glynn]

Dear M.C.

You're not going to be able to do it with a Formula (not unless you're
running EXCEL 2007)

You need a Macro:

Sub CountMe()
For Each d in Range("A1:A100")
If d.Interior.ColorIndex = 3 and d.value = "X" then MyCount = MyCount
+1
Next d
msgbox "There are " & MyCount & " Red X's in the range"
End sub

Give that a go (I haven't tested it yet, just wrote it straight in to
the message group)

Greg

 

[JLGWhiz]

Might work if you change interior to font. Interior is background, font is
character.

 

[JLGWhiz]

In case you have not found a solution, try this one. You will have to re-set
the range parameters to meet your requirements.

Sub redX()
Dim i
A = 0
For Each i In Range("A1:C6").Cells
If i.Value = "x" And i.Font.ColorIndex = 3 Then
A = A + 1
End If
Next i
MsgBox "The count is " & A
End Sub