Excel Sum by Color: Formulas, Filters, and VBA

VBA & macros · Updated November 18, 2026

Excel has no built-in SUM function that adds cells based on their fill color. To total cells of a color, filter the range by color and use SUBTOTAL, or use a VBA function for cells with manually applied fills; if color represents a data category, a formula based on that category is usually more reliable.

Why Excel does not sum by fill color

Excel’s standard functions calculate from cell values, not from formatting. SUM can add numbers in a range, and SUMIF can select numbers based on a value or text criterion, but neither treats a fill color as a normal criterion. A red fill might be applied manually, created by conditional formatting, or used to flag a status that is also recorded in the data. Those are different situations, so there is no single built-in color-summing formula that handles them all.

Color is also a fragile way to store information. A colleague might change a fill, paste a new format, or use a different shade without changing the underlying value. A total based on color can then stop matching the meaning of the worksheet. If the color means “approved,” “overdue,” or “priority,” store that status in a column and calculate from it where possible.

Choose a method for your worksheet

The right approach depends on how the color got there and whether you need a one-time total or a total that updates as the data changes:

  • One-time total of manually colored cells: filter by the fill color, then sum the visible rows.
  • Ongoing total where color represents a category: sum using the category or rule that determines the color.
  • Ongoing total based on manually applied fills: use a VBA user-defined function, and recalculate it when formatting changes.
  • Color is produced by conditional formatting: use the rule’s underlying condition to calculate the total, rather than relying on the displayed fill.

For a shared workbook or a report that must update reliably, the category-based formula is generally the easiest to audit. VBA is useful when the fill itself is genuinely the only available signal, but it adds macro security and maintenance considerations.

Sum cells by color with a filter

Filtering is a practical choice when you need a quick total and do not need a separate formula for each color. It works best when your data has a header row and each row represents one record. In Microsoft 365 for Windows:

  1. Select a cell in the data range, then choose Data > Filter. Excel adds filter arrows to the header row.
  2. Open the filter arrow for the column whose cells are colored.
  3. Choose Filter by Color, then select the fill color you want. The list is reduced to matching rows.
  4. Place a total formula outside the filtered list. With amounts in B2:B20, use =SUBTOTAL(9,B2:B20).

With amounts in B2:B20, that SUBTOTAL formula returns the sum of the visible amounts; for example, if the visible amounts are 12, 18, and 25, the result is 55. Function number 9 sums values while ignoring rows removed by a filter. If you also want the total to exclude rows hidden manually, use function number 109 instead: with amounts in B2:B20, =SUBTOTAL(109,B2:B20) returns the sum of visible rows and skips manually hidden rows too.

Excel for Mac also has filtering and color-filter options, though labels and layout can vary slightly by release. Excel for the web may expose different options from the desktop app, so check whether the filter menu offers the color you need. Filtering changes which rows are displayed; it does not change their fill or permanently remove records.

Sum by the rule behind the color

If a fill indicates a real category, use that category as the formula criterion. For instance, assume A2:A20 contains a status and B2:B20 contains amounts. With “Approved” in the status column, enter =SUMIF(A2:A20,"Approved",B2:B20). If the approved rows contain 40, 35, and 25, the result is 100. The formula keeps working when an amount changes or a new row is included in the ranges, as long as the formula’s ranges are updated to include it.

This approach is especially useful when conditional formatting applies a color because a rule evaluates a value. If the rule colors amounts greater than 100, for example, calculate from that numeric condition rather than trying to add cells that look a certain color. With amounts in B2:B20, a formula for amounts greater than 100 is =SUMIF(B2:B20,">100",B2:B20). It sums the qualifying amounts themselves. If the conditional-formatting rule instead depends on a status column, sum by that status, not by the appearance of the fill.

Keeping the condition and the total tied to the same data rule prevents a formatting change from silently changing the meaning of your calculation. It also makes the result easier to explain to someone reviewing the workbook.

Use VBA for manually filled cells

If cells have manually applied fills and color is the only criterion available, a VBA user-defined function can compare each cell’s fill with a sample cell. This example checks direct cell formatting, not a color displayed only through conditional formatting. In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and add this code to the module:

Function SumByFillColor(SumRange As Range, ColorSample As Range) As Double
    Dim Cell As Range

    Application.Volatile

    For Each Cell In SumRange
        If Cell.Interior.Color = ColorSample.Interior.Color Then
            If Not IsError(Cell.Value) Then
                If IsNumeric(Cell.Value) Then
                    SumByFillColor = SumByFillColor + Cell.Value
                End If
            End If
        End If
    Next Cell
End Function

Return to the worksheet and use a sample cell with the desired fill. With values in B2:B20 and the sample color in D1, enter =SumByFillColor(B2:B20,D1). If matching cells contain 12, 18, and 25, the result is 55. The sample cell can be outside the data range; its value does not matter, only its fill does.

Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) so the code is retained. On Mac, Excel also supports VBA, but the keyboard shortcut for opening the editor can depend on the keyboard and Excel setup; use the Visual Basic command in the app’s menus if needed. Macro availability and security settings can differ between desktop Excel and Excel for the web, which does not run VBA macros.

Understand the VBA and recalculation limits

Application.Volatile asks Excel to recalculate the function whenever a calculation occurs. It does not make a fill-color change a dependable calculation trigger: formatting changes alone may leave the displayed total unchanged. After recoloring a cell, force a calculation with F9 on Windows or Fn+F9 on some Mac keyboards, or edit and re-enter the formula. If the total still appears stale, check that calculation is set to Automatic under Formulas > Calculation Options.

The function compares Interior.Color, which reads a cell’s direct fill formatting. It does not evaluate a fill that appears only because conditional formatting is active. It also compares exact color values: two fills that look similar but use different shades may not match. Copying formats, applying a theme, or changing the sample cell can therefore affect which values are included. These are important reasons to prefer a data-based condition when one exists.

Before running macros or editing VBA in an important workbook, save a separate copy. Changes made by a macro cannot be undone with Ctrl+Z, and a workbook containing VBA should be treated carefully if it is shared with people who do not expect macros.

Common problems and checks

  • The filter menu has no color option: Confirm that the selected range has filter arrows and that the column contains colored cells. If the color comes from conditional formatting, try filtering by the displayed color if available in your Excel version, or calculate from the rule’s condition instead.
  • The total includes rows you thought were hidden: SUBTOTAL with function number 9 excludes filtered rows but includes rows hidden manually. Use 109 when manually hidden rows should also be excluded.
  • The VBA total does not change after recoloring: Recalculate the workbook, then verify that the fill was applied directly and that the sample cell has the exact same color.
  • Conditional-formatting cells are missed by the VBA function: That is expected; the example checks direct fill formatting. Use the rule’s underlying values or criteria for the calculation.
  • The result is too large or too small: Check that the sum range covers only the intended numeric values and that the sample color is not also used for headings, totals, or unrelated records.

Frequently Asked Questions

Can SUMIF sum cells by color? No. SUMIF tests a value or text criterion, not a cell’s fill. Use a category or numeric condition that corresponds to the color, or use a filter or VBA when you truly need to work from formatting.

Can Excel sum cells colored by conditional formatting? The usual reliable method is to sum according to the condition that triggers the formatting. A VBA function that checks Interior.Color reads direct fills, not the conditional-formatting display color, so it will not correctly identify those cells.

Does the color-based VBA formula update automatically? It recalculates when Excel recalculates the workbook, but changing a fill alone may not trigger that calculation. Use F9 on Windows or the appropriate function-key combination on Mac after changing formats, and verify the result.

Can I total a color without macros? Yes. Filter the list by color and use SUBTOTAL to sum visible rows. If the color represents a status or other data condition, a regular SUMIF formula is usually a better macro-free solution.

Will the VBA method work in Excel for the web? Excel for the web does not run VBA macros. Use the desktop Excel app to run the function, or choose a filter-based or data-criteria method that suits your workbook.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

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

Spotted an error in this guide? Let us know.