Tips & Tricks 168 – Sum Cells for a Particular Color

This can be accomplished with VBA only. There is no native formula within Excel to support this. This is a fairly small piece of code..

1. Make a backup of your workbook.
2. Open your workbook and ALT+F11
3. Locate your Workbook name in Project Explorer Window
4. Right click on your workbook name > Insert > Module
5. Copy and Paste the below code in the module
6. Save your file as .xlsm

7. Call your macro as

=SumColorCells(A1:C6,255,255,0)

In place of A1:C6, your can put your range.

Next 3 digits are RGB codes. For Yellow, they are 255, 255 and 0…To know the RGB code of any colour, select your cell and click on down pointing arrow on Fill Colour symbol and go to custom to pick up RGB code.

—- Macro by E for Excel —-

Function SumColorCells(Rng As Range, R, G, B)
    Dim Cell As Range
    Application.Volatile
    For Each Cell In Rng
        If IsNumeric(Cell) And Cell.Interior.Color = RGB(R, G, B) Then
            SumColorCells = SumColorCells + Cell
        End If
    Next Cell
End Function