Sum by color
2002-06-26 Sum 0 176
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.