Sumif on cells color

Posted by Sam Brown on May 25, 2001 4:15 AM

I'm sure this can be done but i'm not sure how.

I got cells with various colors/numbers and I would like to add up those which are red, etc.

thanks

Sam

Posted by Dave Hawley on May 25, 2001 4:45 AM

Hi Sam

I have a custom function that I have written for this. To use push Alt+F11 and go to Insert>Module and paste in this code:

Function SumColor(rColor As Range, rSumRange As Range)
'Written by Ozgrid Business Applications
'www.ozgrid.com
Dim rCell As Range
Dim iCol As Integer
Dim vResult
iCol = rColor.Interior.ColorIndex

For Each rCell In rSumRange
If rCell.Interior.ColorIndex = iCol Then
vResult = WorksheetFunction.Sum(rCell) + vResult
End If
Next rCell

SumColor = vResult
End Function

Then push Alt+Q to return to Excel and Save.

Now in any cell put:

=SumColor(A10,A1:A20)

Where A10 interior Color is Red (or any other color) and A1:A20 is the range you want to Sum.

Dave

OzGrid Business Applications

Posted by CogswelCogs on May 29, 2001 7:04 AM

You can write a function using this for ex.
I dont think you can do it outside of VBA

If Cells(HC, xcol).Interior.ColorIndex = 8 Then