Counting by color

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

 

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

 

[Gord Dibben]

In addition to the other replies.

How are the cells colored? Manually or CF?

If by Conditional Formatting, colorindex is not recognized and you need a
whole bunch more code from Chip's site.

http://www.cpearson.com/excel/CFColors.htm


Gord Dibben MS Excel MVP