how to make excel simply count red x's?
 

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.

how to make excel simply count red x's?
 

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.

how to make excel simply count red x's?
 

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.

how to make excel simply count red x's?
 

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.

how to make excel simply count red x's?
 

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.