Counting by color

D

Danny Boy

I saw this formula on Chip Pearson's site. According to Chip:

You can use the ColorIndexOfRange function to get the sum of the values in
those cells whose color index is some specified value. For example, the
following array formula will sum the values of the cells in range B11:B17
whose fill color is red.

=SUM(B11:B17*(COLORINDEXOFRANGE(B11:B17,FALSE,1)=3))

I tried to use this formula in a spreadsheet, but received a#NAME? error.
I'm trying to count all occurences in a range of cells that are in red font,
but I'm not having much luck.
 
T

Tom Hutchins

The ColorIndexOfRange function doesn't come with Excel; it's a user-defined
function (UDF) written by Chip Pearson. You have have to add it to each
workbook where you want to use it. The code is written in Visual Basic for
Applications (VBA) for Excel, and is given earlier on the same page as your
quote.

Jon Peltier's site has good instructions on how to add any UDF or macro to
your workbook:
http://peltiertech.com/WordPress/2008/03/09/how-to-use-someone-elses-macro/

Hope this helps,

Hutch
 
L

L. Howard Kittle

Try this garage version. You will have to name the range you want to count
the fonts in to Data. (or any name you wish, but include THAT name in the
code)

This counts three colors and you may want to omit (delete from the code)
those you don't want.

I would add, it seems I remember Chip's site also offers a font color count
solution. His, along with his excellent narrative at the site would surely
be a better choice.

Sub FontColorCount()
'Counts the number of colored
'fonts in a range named Data.
Dim cell As Range
Dim Blue5 As Integer, Red3 As Integer, _
Green4 As Integer, Yellow6 As Integer

For Each cell In Range("Data")
If cell.Value <> "" _
And cell.Font.ColorIndex = 5 Then
Blue5 = Blue5 + 1
ElseIf cell.Value <> "" _
And cell.Font.ColorIndex = 3 Then
Red3 = Red3 + 1
ElseIf cell.Value <> "" _
And cell.Font.ColorIndex = 4 Then
Green4 = Green4 + 1
ElseIf cell.Value <> "" _
And cell.Font.ColorIndex = 6 Then
Yellow6 = Yellow6 + 1
End If
Next

Range("A1").Value = Blue5 & " Blue"
Range("A2").Value = Red3 & " Red"
Range("A3").Value = Green4 & " Green"
Range("A4").Value = Yellow6 & " Yellow"

MsgBox " You have: " & vbCr _
& vbCr & " Blue " & Blue5 _
& vbCr & " Red " & Red3 _
& vbCr & " Green " & Green4 _
& vbCr & " Yellow " & Yellow6, _
vbOKOnly, "CountColor"
End Sub

HTH
Regards,
Howard
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top