Adding ONLY cells of certain color

[Guest]

Hello!
Can anyone tell me how to add only cells of a certain color? Whether it's
the cell's background color, or the font color, doesn't really matter. I
researched and seemed like SumIF was the correct function, but I can't figure
out how to specify only certain colors.....
Thanks for your help.

 

[Bernard Liengme]

Visit Chip at http://www.cpearson.com/excel/colors.htm

 

[Guest]

Enter this UDF:

Function SumRed(r As Range) As Long
Dim rr As Range
SumRed = 0
For Each rr In r
If rr.Interior.ColorIndex = 3 Then
SumRed = SumRed + rr.Value
End If
Next
End Function

and use it like:
=SumRed(A1:G4)

It will sum those cells that have a red background. If you change the
cell's background after you have entered the formula, you will need to
manually re-calculate the worksheet:

CNTRL-ALT-F9