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:
- Select a cell in the data range, then choose Data > Filter. Excel adds filter arrows to the header row.
- Open the filter arrow for the column whose cells are colored.
- Choose Filter by Color, then select the fill color you want. The list is reduced to matching rows.
- 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
- Excel Not Keeping Formatting? Causes and Fixes
- Excel Not Blank Conditional Formatting: How to Set It Up
- Excel Not Blank Symbol: What <> Means
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- View Macros in Google Sheets: Find and Read the CodeTo view a macro in Google Sheets, open the spreadsheet in a web browser and choose Extensions >…
- Excel Macro File: How to Save, Open, and Use It SafelyAn Excel macro file is a workbook that can store VBA code to automate tasks; the usual format…
- Excel VBA Resize: Change a Range’s Size with VBA CodeIn Excel VBA, use the Resize property to refer to a range with a different number of rows…
- Excel Macro For Loop: Examples and Common FixesAn Excel macro for loop is a VBA loop that repeats instructions for a set of items or…
- VBA Outlook Create Contact Group from ExcelYes. In classic Outlook for Windows, an Excel VBA macro can read email addresses from a worksheet, create…
- VBA Code Workbook Open: How to Run Macros at StartupTo run VBA when an Excel workbook opens, put a Workbook_Open event procedure in that workbook’s ThisWorkbook code…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.