Surfing the web today, I came across a great user defined function (UDF) called sumcolor(). This function sums a range of cells based on the color of the cells. My head began to spin with the possibilities! Below is the VBA for the function. If you are unfamiliar with how to install a UDF, here is a User Defined Installation Guide.

Function SumColor(rColor As Range, rSumRange As Range)
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

To use the function, the format is =sumcolor(A1, A2:B100). Where A1 is the cell color you want to count and A2:B100 is the range of cells to check for the color and sum.


Joseph Harris
Excel Help
More Excel Help

Related posts:

  1. Conditional Formatting with Multiple Criteria in Excel Microsoft Excel has a limit of 3 criteria in Conditional...
  2. Excel CELL () Function The CELL function can be used to find out information...
  3. ColorIndex Reference – Excel VBA A chart that can be used to look up the...

This entry was posted on Wednesday, February 18th, 2009 at 3:24 pm and is filed under Excel Quick Tricks. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply

You must be logged in to post a comment.