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