Sum cells by fill color in Excel

Sum a color without VBA

Suppose B2:B10 contains amounts and manually applied fills. Add a filter, choose the desired fill under Filter by Color, and enter this outside your table:

=SUBTOTAL(109,B2:B10)

109 sums visible numeric values and ignores manually hidden rows. It does not exclude hidden columns. If another column carries the color, filter that column and keep the formula pointing at the amounts.

Create the SumColor function

Open the Visual Basic Editor, insert a standard module, and paste this code. Save your file as an Excel Macro-Enabled Workbook (.xlsm).

Option Explicit

Public Function SumColor(ByVal cells As Range, _
                         ByVal sample As Range) As Double
    Application.Volatile
    Dim cell As Range
    Dim total As Double
    For Each cell In cells.Cells
        If cell.Interior.Color = sample.Cells(1, 1).Interior.Color Then
            If Not IsError(cell.Value2) Then
                If VarType(cell.Value2) <> vbString And _
                   VarType(cell.Value2) <> vbBoolean Then
                    If IsNumeric(cell.Value2) Then
                        total = total + CDbl(cell.Value2)
                    End If
                End If
            End If
        End If
    Next cell
    SumColor = total
End Function

This implementation adds numeric values only. It skips errors, text (including numeric text such as "100"), blanks, and Boolean values. Check source errors separately before relying on a total.

Call it from the sheet

Give E2 the fill you want to sum, then enter:

=SumColor(B2:B10,E2)

The function includes hidden rows. It compares the amount cell’s fill directly with E2, so it will not use a status color stored in a different column.

Prefer a status column for ongoing work

If a color means Paid, store Paid in a status column and calculate from that value. You can then use the same rule to apply conditional formatting:

=SUMIFS(B2:B10,C2:C10,"Paid")

Download the example

A VBA source module, not a workbook with embedded macros. Read the code and follow the setup steps above.