Excel Sum by Cell Color: Methods That Actually Work

Excel does not have a built-in SUMIF function that adds cells based on fill color. To total one color, filter the range by that color and use SUBTOTAL, create a helper column that records the category behind the color, or use a VBA function if you specifically need to inspect cell fills.

Why Excel cannot SUMIF by fill color

Functions such as SUM and SUMIF evaluate cell values, not ordinary formatting. A cell filled red and a cell filled blue are indistinguishable to those functions if their contents are the same. Color is presentation applied to a cell, rather than a value that a regular worksheet formula can test.

This distinction matters because a fill can be applied manually, copied from another cell, or produced by a conditional-formatting rule. A formula based on a business condition—such as “overdue,” “approved,” or “sales above target”—is usually more dependable than a formula based on how the cell happens to look. If the color is only a visual label, however, filtering by color or using VBA can be practical.

Quick method: filter by color and use SUBTOTAL

For a one-time total, Excel’s filter controls are often the simplest option. This does not create a formula that recognizes color; it hides rows that do not match the chosen color, and SUBTOTAL adds the visible values.

Make sure the data has a header row, such as Amount, and that the colored cells are in the column you want to filter.
Select a cell in the data and choose Data > Filter.
Open the filter arrow for the colored column, choose Filter by Color, then select the fill color to keep.
With amounts in B2:B5, where the red cells contain 120 and 150, put this formula in an unfiltered area or below the data: =SUBTOTAL(109,B2:B5).

With that layout, the result is 270: the filter leaves the two red rows visible, and 120 + 150 = 270. The 109 option means SUM while excluding manually hidden rows as well as rows hidden by filtering. The similar option 9 also ignores filtered-out rows, but includes manually hidden rows. If a total row is inside the filtered range, Excel’s SUBTOTAL behavior avoids counting nested subtotals again.

This approach is quick and leaves the source values untouched, but it is interactive: the displayed total changes when the filter changes. It is not a separate total for every color, and filtering a table affects which records are visible to anyone viewing that sheet.

Best ongoing method: store the category in a helper column

If color represents a real category, record that category as data. For example, put a status such as Red, Amber, or Green in column C, then apply conditional formatting or a consistent manual fill based on the status. This gives the workbook a reliable value to calculate, sort, validate, and audit.

With status labels in C2:C5, amounts in B2:B5, and Red assigned to the rows containing 120 and 150, use =SUMIF(C2:C5,"Red",B2:B5). The result is 270 because the formula adds the amounts for rows whose status equals Red. The criterion is text, so it must be in quotation marks; the sum range contains the amounts.

Unlike a color-based method, this total updates when the status values or amounts change. It also makes the workbook easier to understand: another person can tell why a row is included without relying on a visual convention. If users currently type colors manually, consider adding a drop-down with Data > Data Validation and applying conditional formatting to the chosen status.

Use VBA when the fill itself is the requirement

A VBA user-defined function can compare each cell’s direct fill color with a sample cell and add matching numeric values. This suits a workbook where color is already the only available label, but it requires a macro-enabled workbook and has important recalculation limits.

For the example below, put the values to total in B2:B20 and use one sample cell, such as D1, filled with the color to match. In Excel for Microsoft 365 on Windows, press Alt+F11, choose Insert > Module in the Visual Basic Editor, and paste this code:

Option Explicit

Function SumByFillColor(rangeToSum As Range, colorSample As Range) As Double
    Dim cell As Range
    Dim total As Double

    Application.Volatile True ' Recalculate when Excel runs a calculation.

    For Each cell In rangeToSum.Cells
        If Not IsError(cell.Value2) Then
            If IsNumeric(cell.Value2) Then
                If cell.Interior.Color = colorSample.Interior.Color Then
                    total = total + CDbl(cell.Value2)
                End If
            End If
        End If
    Next cell

    SumByFillColor = total
End Function

With values in B2:B20 and the comparison fill in D1, enter =SumByFillColor(B2:B20,D1) in a worksheet cell. If the matching cells contain 120 and 150, the result is 270. Save the file as an Excel Macro-Enabled Workbook (.xlsm) and enable macros only when you trust the workbook and its source.

Before running or adding a macro, save a copy of the workbook. VBA changes cannot be undone with Ctrl+Z. On a Mac, the Visual Basic Editor is available in Excel, but keyboard shortcuts and some menu details differ from Windows; Excel for the web cannot create or run VBA macros.

Conditional formatting changes what a color formula can see

The VBA example checks Interior.Color, which is the cell’s directly applied fill. Conditional formatting draws a displayed fill according to a rule; it does not replace the underlying direct fill in the same way. As a result, the function can return a total that does not match the color currently visible on screen when conditional formatting is responsible for that color.

For conditional colors, calculate from the rule’s underlying condition instead. If cells turn red when their status is Overdue, sum rows whose status is Overdue rather than trying to total the displayed red fill. If the color rule is “amount is greater than 100,” use that numeric condition as the basis of the calculation. The criteria remain explicit and update naturally as the values change.

There is another limitation with VBA: changing a cell’s fill does not normally trigger worksheet recalculation. Application.Volatile asks Excel to recalculate the function when a calculation occurs, but it does not make a format change itself a calculation event. After changing fills, force a full calculation with Ctrl+Alt+F9 on Windows. On Mac, use the equivalent Excel calculation command or recalculate the workbook; shortcut mappings can vary by keyboard and Excel version.

Which method should you choose?

Use Filter by Color and SUBTOTAL for a quick check or a temporary report where showing one color at a time is acceptable.
Use a helper column and SUMIF when colors represent statuses or categories that should remain accurate as the workbook changes. This is generally the clearest choice for shared or important workbooks.
Use VBA when you must total existing direct fills and cannot replace them with meaningful data labels. Be prepared to manage macros and refresh calculations after formatting changes.
Do not rely on fill color as the sole record when the total supports financial, operational, or compliance decisions. Keep the underlying category or condition in a cell.

Also consider how the workbook will be used. Recipients may have macros disabled, and Excel for the web does not run VBA. A helper-column solution is more portable and easier to inspect. A filtered subtotal needs no macro, but viewers must understand that the result follows the current filter.

Common problems and checks

If a color total appears wrong, first check what the fill actually is. A cell can look colored because of conditional formatting, a table style, or a manually applied fill. The VBA function above compares direct fills only; a visible color caused by a rule may not match the cell’s stored Interior color.

The total does not update after recoloring: recalculate the workbook; a formatting change alone does not reliably trigger a VBA worksheet function.
The result is zero: confirm the sample cell has the intended direct fill, that the range includes the colored values, and that the values are numeric rather than numbers stored as text.
Two similar colors behave differently: VBA compares color values, not how similar two shades look on a monitor. A theme color, tint, or nearby shade may be a different color value.
The formula returns an error or is unavailable: confirm macros are permitted, the code is in a standard module, and the workbook is saved as .xlsm. The UDF is VBA, not a built-in Excel function.
The visible red rows do not match the result: check whether conditional formatting creates the red appearance and calculate from the rule’s underlying condition instead.

For the filter method, verify that the filter covers the complete data range and that the correct column’s filter arrow was used. A manually hidden row can also affect SUBTOTAL: function number 109 excludes manually hidden rows, while 9 does not. These details explain many apparent differences between a filtered total and a hand-added set of values.

Frequently Asked Questions

Can I use SUMIF directly with a cell color?

No. SUMIF tests cell contents against a criterion; it does not test ordinary fill color. Use a helper column that stores the category, filter the data by color and use SUBTOTAL, or use VBA to inspect direct fills.

Can Excel sum cells colored by conditional formatting?

Do not use the direct-fill VBA function for this: it checks Interior.Color, not the displayed conditional-formatting result. Sum according to the condition that creates the color, such as a status value or a numeric threshold.

Does the VBA total update as soon as I change a fill?

Not necessarily. Excel does not generally recalculate a worksheet function just because formatting changed. Recalculate after recoloring, and remember that Application.Volatile only refreshes the function during a calculation.

Can I do this without macros?

Yes. Filter the target column by color and use SUBTOTAL for a quick total, or store the category in a helper column and use SUMIF. The helper-column approach is usually best when the result needs to update automatically from data changes.

Will the VBA method work in Excel for the web?

No. Excel for the web does not run VBA macros. Use a helper column or a filter-based subtotal if the workbook must calculate in the browser or be shared with people who cannot enable macros.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.