Excel Sum by Color: How to Total Cells by Fill Color

Excel for Microsoft 365 does not include a built-in SUMBYCOLOR formula. The simplest dependable method is to filter the rows by fill color and use SUBTOTAL to add the visible values; for repeatable reports, use a helper column or a VBA function, depending on how the colors are applied.

Why Excel cannot sum colors with an ordinary formula

Excel formulas normally calculate from cell contents—numbers, text, dates, and logical conditions—not from a cell’s appearance. A fill color is formatting, so a formula such as SUMIF cannot directly ask whether a cell is blue or yellow. This is why typing a color name into a regular worksheet formula does not make Excel total cells with that fill.

Color can still be useful for organizing a worksheet, but it is a fragile stand-in for data. Two cells may look the same while having different formatting rules, and a color change does not necessarily trigger formula recalculation. If color represents a meaningful status such as “approved” or “late,” storing that status in a column is usually more reliable than making the fill itself the data.

Fastest method: filter by color and subtotal

Filtering works well when each colored cell belongs to a row of data and you want a quick total for one color. In Excel for Microsoft 365 on Windows, select the data range and choose Data > Filter. Open the dropdown in the column that contains the colored cells, choose Filter by Color, and select the fill color. Excel hides rows that do not match.

With values in B2:B20 and the color you want filtered in another column of the same rows, enter the following formula in a cell outside the filtered list:

=SUBTOTAL(109,B2:B20)

If filtering leaves 12, 18, and 25 visible in B2:B20, the result is 55. The 109 option tells SUBTOTAL to sum while excluding filtered-out rows and manually hidden rows. If you instead use the function number 9, manually hidden rows are included, although filtered-out rows are still excluded.

Make sure each value and its color-coded category are on the same row, with a header row if you are using filters.
Turn on the filter, then filter the color column by the fill you want to total.
Read the SUBTOTAL result, or enter the formula above in a separate cell.
Clear the color filter when you want to see the full list again.

Filtering is a practical choice for occasional checks, but it is not a permanent color-aware total: changing the filter changes the displayed rows, while changing a fill alone does not add color logic to the formula. On Mac, the filter and SUBTOTAL approach is also available, though ribbon layout can vary by Excel release. Excel for the web supports basic table filtering, but some filter options can differ from desktop Excel.

Use a helper column for a durable report

If the color communicates a category, put that category in a dedicated column as well. For instance, record Blue, Yellow, or Red in column C and use conditional formatting or manual formatting to color the cells. The label is then the source of truth; the fill simply makes the report easier to scan. This approach works in Excel for the web as well as desktop Excel and does not depend on macros.

With category labels in C2:C20, amounts in B2:B20, and the label Blue identifying the rows to add, use:

=SUMIF(C2:C20,"Blue",B2:B20)

If the blue category has amounts of 12, 18, and 25, the formula returns 55. SUMIF checks the labels in C2:C20 and adds the matching values from B2:B20. You can change the criterion to another label or put the label in a cell and refer to that cell instead. The important point is that the formula evaluates the category text, not the blue fill.

A helper column is especially useful when colors may change, when several people edit the workbook, or when totals need to feed a chart or another formula. It also makes the rule easier to review: a colleague can see why a row is counted without needing to inspect its formatting.

VBA option for cells colored by hand

If you specifically need a total based on manually applied fill colors, a custom VBA function can compare each cell’s fill with a sample cell. This is appropriate for a desktop workbook where macros are allowed, but it is not a built-in Excel function and does not work as a normal formula in Excel for the web.

Assume the amounts to add are in A2:A20 and D1 is a sample cell with the fill color to match. First open the Visual Basic Editor with Alt+F11 on Windows, then choose Insert > Module and add this function:

Function SumByColor(sumRange As Range, colorSample As Range) As Double
    Dim cell As Range
    Dim targetColor As Long
    Dim total As Double

    ' Compare each cell's direct fill with the sample fill.
    targetColor = colorSample.Interior.Color
    For Each cell In sumRange.Cells
        If cell.Interior.Color = targetColor Then
            If Not IsError(cell.Value) Then
                If IsNumeric(cell.Value) And Not IsEmpty(cell.Value) Then
                    total = total + CDbl(cell.Value)
                End If
            End If
        End If
    Next cell

    SumByColor = total
End Function

Save the workbook as an Excel Macro-Enabled Workbook (.xlsm), and enable macros only if you trust the workbook and its source. With amounts in A2:A20 and the desired fill applied to D1, use this worksheet formula:

=SumByColor(A2:A20,D1)

If the matching numeric cells in A2:A20 contain 12, 18, and 25, the function returns 55. It checks direct cell fill colors, ignores error values, and adds numeric values, including numeric formula results. A sample cell with no fill may match cells with the same underlying default fill; use a distinctive applied fill if you need to avoid ambiguity.

There are important limits. A worksheet function does not automatically recalculate just because you changed formatting. After recoloring cells, use Formulas > Calculate Now or otherwise force a recalculation to refresh the result. Also, this code reads Interior.Color, which is the direct fill, not necessarily the color currently displayed by conditional formatting. Mac Excel has VBA support in desktop editions, but macro availability and editor shortcuts differ; this function is not available in Excel for the web. Keep a copy before using or modifying VBA: macro actions cannot be undone with Ctrl+Z.

Conditional formatting needs a different approach

Conditional formatting applies a displayed fill when a rule is true—for example, when a due date is earlier than today or a score is below a threshold. That displayed color is an effect of the rule, not a dependable value for an ordinary SUMIF or the VBA function above. A cell can also have a direct fill underneath a conditional format, which makes “sum by the color I see” ambiguous.

The better solution is to total using the condition that creates the color. If rows are colored red when a status column says Late, sum or count the rows whose status is Late. If color is based on a numeric threshold, use the same threshold as the formula criterion. This keeps the total synchronized with the rule and avoids a manual recalculation step after formatting changes.

When the visual rule is complex, document it and build the total from its underlying tests. If several different rules can produce the same displayed color, decide whether the total should include all those cases or only one specific condition; color alone may not reveal that distinction.

Which method should you choose?

Filter by color with SUBTOTAL: Best for a quick, interactive total when colored records are rows in a list.
Helper column with SUMIF: Best for ongoing workbooks, shared files, and reports that need transparent, dependable criteria.
VBA function: Best only when the actual manually applied fill is the criterion and desktop macros are acceptable.
Conditional-formatting rule: Reuse the rule’s underlying data condition rather than trying to total the displayed color.

For a one-time check, filtering is usually the least complicated. For a workbook others will rely on, a category or status column is generally easier to maintain than a color-sensitive macro. Consider whether the total needs to update immediately, whether the file will be opened in a browser, and whether users are permitted to enable macros before choosing.

Common problems and checks

The total stays the same after recoloring: A normal formula does not monitor formatting. A VBA user-defined function may need a recalculation after a fill change.
Conditional-format colors are missing: The VBA example checks direct fills, not the visible result of conditional formatting. Use the rule’s data condition instead.
Rows have the right color but the wrong total: Check that the amount and color-coded category occupy the same row and that the filter is applied to the correct column.
A formula returns zero: In the helper-column method, check that the category text matches the criterion. In the VBA method, check that the sample cell has the intended direct fill and that the amount cells contain numbers.
Different shades are not counted together: Color matching is based on the fill value, not on whether shades look similar to a person. Use one consistent fill or categorize the rows explicitly.
The macro formula shows a name error: Confirm that the function is in a standard VBA module, macros are enabled, and the workbook is saved in a macro-enabled format.

Frequently Asked Questions

Can SUMIF sum cells by fill color? No. SUMIF checks cell values against a criterion; it does not inspect fill formatting. Put the category or status in a helper column and use SUMIF on that field, or filter by color and use SUBTOTAL.

Is there a built-in Excel SUMBYCOLOR function? No. Excel for Microsoft 365 does not provide a native worksheet function with that name. A VBA function can be added to a macro-enabled desktop workbook, but it has limitations and is not a standard Excel formula.

How do I total colored cells without VBA? If the colors mark rows in a list, filter the color column and use SUBTOTAL on the amount range. If the colors represent categories, add category labels and total those labels with SUMIF.

Can Excel sum cells colored by conditional formatting? Not reliably by reading the displayed fill as though it were cell data. Use the value or condition that triggers the conditional formatting as the formula criterion instead.

Why does my color-sum formula not update when I change a fill? Formatting changes do not normally trigger worksheet recalculation. With a VBA color function, run a calculation after changing the fill. A helper-column formula avoids this issue because it responds to the category value.

Does the VBA method work in Excel for the web? No. Excel for the web does not run VBA macros or custom VBA worksheet functions. Use filtering or a helper column there, or open the macro-enabled workbook in a supported desktop version of Excel.

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.