Excel by default has no formula or feature to make a sum based on cell background color.

Function SumByColor(CellColor As Range, rRange As Range)
Dim cSum As Double
Dim ColIndex As Integer
ColIndex = CellColor.Interior.ColorIndex
For Each cl In rRange
If cl.Interior.ColorIndex = ColIndex Then
cSum = WorksheetFunction.Sum(cl, cSum)
End If
Next cl
SumByColor = cSum
End Function

How to use this UDF:

Follow the below steps to use this Function:

  1. Open your target worksheet.
  2. Press ALT + F11 to open the VBA Editor and navigate to ‘Insert’ > ‘Module’.
  3. Paste the “SumByColor” Function in the Editor.

Ref: https://exceltrick.com/how_to/sum-cells-based-on-background-color/

Leave a Reply

Your email address will not be published. Required fields are marked *