Can I count or sum cells based on text color?

[Ron]

If I have certain numbers in a column, in red text, can I sum or count only
those cells?

Thanks,

Ron

 

[Laroche J]

Ron wrote on 2009-04-13 18:26:

If I have certain numbers in a column, in red text, can I sum or count only
those cells?

Thanks,

Ron

There's no real built-in way. If you have Excel v.X or Excel 2004, you could
build custom functions.

Function CountIfColor(MyRange As Range, MyColor As Integer)
CountIfColor = 0
For Each c In MyRange.Cells
If c.Font.ColorIndex = MyColor Then
CountIfColor = CountIfColor + 1
End If
Next
End Function

Function SumIfColor(MyRange As Range, MyColor As Integer, SumRange As Range)
SumIfColor = 0
RangeOffset = SumRange.Row - MyRange.Row
For Each c In MyRange.Cells
If c.Font.ColorIndex = MyColor Then
SumIfColor = SumIfColor + SumRange.Parent.Cells(c.Row + _
RangeOffset, SumRange.Column).Value
End If
Next
End Function

Use as you would COUNTIF and SUMIF, except that the second argument is the
color index you're looking for. The brightest shade of red has value 3. This
function could help find the color index of a target cell:

Function FontColor(MyRange As Range)
FontColor = MyRange.Font.ColorIndex
End Function

Put these functions in one of the workbook's modules.

JL
Mac OS X 10.4.11
Office v.X 10.1.9, Office 2008 trial

 

[Ron]

I'll give it a try. Works in 2007 also?

Ron

Ron wrote on 2009-04-13 18:26:


There's no real built-in way. If you have Excel v.X or Excel 2004, you could
build custom functions.

Function CountIfColor(MyRange As Range, MyColor As Integer)
CountIfColor = 0
For Each c In MyRange.Cells
If c.Font.ColorIndex = MyColor Then
CountIfColor = CountIfColor + 1
End If
Next
End Function

Function SumIfColor(MyRange As Range, MyColor As Integer, SumRange As Range)
SumIfColor = 0
RangeOffset = SumRange.Row - MyRange.Row
For Each c In MyRange.Cells
If c.Font.ColorIndex = MyColor Then
SumIfColor = SumIfColor + SumRange.Parent.Cells(c.Row + _
RangeOffset, SumRange.Column).Value
End If
Next
End Function

Use as you would COUNTIF and SUMIF, except that the second argument is the
color index you're looking for. The brightest shade of red has value 3. This
function could help find the color index of a target cell:

Function FontColor(MyRange As Range)
FontColor = MyRange.Font.ColorIndex
End Function

Put these functions in one of the workbook's modules.

JL
Mac OS X 10.4.11
Office v.X 10.1.9, Office 2008 trial

 

[Ron]

Ok, FontColor and CountIfColor work fine.

SumIfColor returns a #Value error on the same cells. Triple checked typing.

Also, if I write this in Personal.xls, won't it be available to all
workbooks I open, as long as Personal is open?

Thanks,
Ron

 

[Laroche J]

Ron wrote on 2009-04-14 16:17:

Ok, FontColor and CountIfColor work fine.

SumIfColor returns a #Value error on the same cells. Triple checked typing.

Also, if I write this in Personal.xls, won't it be available to all
workbooks I open, as long as Personal is open?

Thanks,
Ron

Oh!, you are on Windows. This is a forum for Excel on the Mac.

Nevertheless, use this improved version (do a copy and paste to make sure
everything is as I typed):

Public Static Function SumIfColor(MyRange As Range, MyColor As Integer, _
Optional SumRange As Variant)
If IsMissing(SumRange) Then
Set SumRange2 = MyRange
Else
Set SumRange2 = SumRange
End If
SumIfColor = 0
MyRangeTopRow = MyRange.Cells(1, 1).Row
MyRangeLeftColumn = MyRange.Cells(1, 1).Column
For Each c In MyRange.Cells
If c.Font.ColorIndex = MyColor Then
RowOffset = c.Row - MyRangeTopRow
ColumnOffset = c.Column - MyRangeLeftColumn
SumIfColor = SumIfColor + SumRange2.Cells(1, 1). _
Offset(RowOffset, ColumnOffset).Value
End If
Next
End Function


There's still a problem I haven't solved: after typing the formula the value
calculates, but if I change the values of the cells to be added the function
doesn't recalculate by itself. I'm working on it. I don't know if it's the
same on Windows.

JL
Mac OS X 10.4.11
Office v.X 10.1.9, Office 2008 trial

 

[Ron]

I apologize. I somehow never noticed which group it was in. Don't know how I
got here, either. Bookmarked this years ago. Luckily, this is only my second
post. Guess I need to go find the "other" group.

However, thanks for the solutions. Looks like they mostly work in both.

Ron

 

[CyberTaz]

Here ya go, Ron:

http://www.microsoft.com/office/community/en-us/FlyoutOverview.mspx

But you may not need the VBA in Excel 2007. The Tables & AutoFilter features
may provide the "based on color" functionality you want.

HTH |:>)
Bob Jones
[MVP] Office:Mac

 

[Ron]

Thanks for the link. I found the correct group. Geezz

I'll check again, but did not see where I could sum or count in those
functions according to color.

Ron

 

[CyberTaz]

Have a look at the "new & improved" AutoFilter which is also incorporated
into the Table feature.

Regards |:>)
Bob Jones
[MVP] Office:Mac