Sum by color

 2002-06-26    Sum    0    47

It's not possible to add the values in a range depending on each cells background color with the built-in SUM()-function. With the custom function below you can add the contents of cells depending on their background color:

Function SumByColor(InputRange As Range, ColorRange As Range) As Double
' returns the sum of each cell in the range InputRange that has the same
' background color as the cell in ColorRange
' example: =SumByColor($A$1:$A$20,B1)
' range A1:A20 is the range you want to sum
' range B1 is a cell with the background color you want to sum
Dim cl As Range, TempSum As Double, ColorIndex As Integer
    ' Application.Volatile ' this is optional
    ColorIndex = ColorRange.Cells(1, 1).Interior.ColorIndex
    TempSum = 0
    On Error Resume Next ' ignore cells without values
    For Each cl In InputRange.Cells
        If cl.Interior.ColorIndex = ColorIndex Then 
            TempSum = TempSum + cl.Value
        End If
    Next cl
    On Error GoTo 0
    Set cl = Nothing
    SumByColor = TempSum
End Function
The function is used in the same way as the built-in worksheetfunctions. InputRange contains the cells with the values that the function is going to add, ColorRange is a cell reference to a cell containing the background color you want restrict the adding to.
In Excel 5/95 the variables InputRange and ColorRange must be declared as Variant instead of Range. The function can easily be adapted for use with other worksheetfunctions that you want to use on cells with different background colors.


Leave a comment:

Your comment will only be published after it has been moderated and found spam free.
Your e-mail address will only be used to display your Gravatar.