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.

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)

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

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

wrote:
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.

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

"Greg Glynn" wrote:

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

wrote:
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.

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

" wrote:

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.