Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to sum cells by color in Excel and Google Sheets
Topic:SUM & aggregationExcel & Google Sheets
=SUMIF(B2:B5,"Red",C2:C5)✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Item | Color Label | Amount |
| Apple | Red | 100 |
| Blueberry | Blue | 200 |
| Strawberry | Red | 150 |
| Grape | Blue | 180 |
=SUMIF(B2:B5,"Red",C2:C5)→250
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Add a helper column (e.g. column B) next to the colored values column. Type a color label — "Red", "Blue", "Green" — in each row to mirror the fill colors applied.
- 2In an empty cell enter =SUMIF(B2:B5,"Red",C2:C5) to sum column C values where the helper column equals "Red".
- 3Press Enter. Repeat with different criteria for each color you need: =SUMIF(B2:B5,"Blue",C2:C5), etc.
- 4Excel VBA alternative: open the VBA editor (Alt+F11), Insert → Module, define `Function SumByColor(colorCell As Range, rng As Range) As Double` that loops checking `cell.Interior.Color = colorCell.Interior.Color`. Save as .xlsm. Use as =SumByColor($A$1, C2:C5) where A1 is a sample cell with the target color.
- 5Google Sheets Apps Script alternative: Extensions → Apps Script, paste a custom function using SpreadsheetApp.getActiveSheet().getRange(...).getBackgrounds(). Use as =SumByColor(C2:C5, "#FF0000").
Tips
- Helper-column wins by default. It works in every Excel version, survives copy-paste between files, doesn't require macro permissions, and recalculates automatically when values change.
- VBA and Apps Script color functions do NOT recalculate when colors change — only when values change. Press F9 in Excel or re-run the script in Sheets to force recalc after recoloring.
- To count instead of sum: swap SUMIF for COUNTIF — =COUNTIF(B2:B5,"Red") counts rows labeled Red.
- If colors come from conditional formatting (not manual fill), the macro reads the underlying cell color which is usually white — not what you want. Use the conditional rule's logic directly in the SUMIF criteria instead.
- In Excel, the SUBTOTAL function with a filter-by-color applied to the data range will sum only the visible (filtered-in) rows: filter by color first, then =SUBTOTAL(9, C2:C100).
- For one-time analysis without setting up helpers, filter the column by color (Filter → Filter by Color) and use the status bar to see the sum of selected cells — quick and no formula needed.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
Is there a native SUMBYCOLOR function in Excel or Sheets?
No. Neither application exposes cell fill color to worksheet formulas natively, even in Excel 365 / 2024. You must use a helper column, VBA macro, or Google Apps Script.
Will a VBA sum-by-color function work in Google Sheets?
No — VBA is Excel-only. Sheets uses Apps Script (JavaScript-based). The equivalent: Extensions → Apps Script → write a custom function that uses range.getBackgrounds() to read the color hex, then sum matching cells.
Why doesn't my SumByColor function update when I change a cell color?
Color changes don't trigger a recalculation. Either force a recalc (F9 in Excel, Ctrl+Shift+F9 in Sheets) after recoloring, or add a volatile reference like `+NOW()*0` to the formula so it re-evaluates on every workbook change (warning: degrades performance on large sheets).
Can I sum by color from conditional formatting rules?
Not directly. Macros read the underlying fill color, which conditional formatting doesn't change. Use the conditional rule's logic in SUMIF instead — e.g. if the rule highlights cells > 100, use =SUMIF(C2:C100,">100",C2:C100).
How do I sum based on font color instead of fill color?
Same approach with a different VBA property: replace Interior.Color with Font.Color in the macro. The helper-column approach works identically — tag rows by category and SUMIF.
What's the simplest one-time approach without formulas?
Apply Filter → Filter by Color to the value column, then look at the Sum in the status bar (Excel) or use =SUBTOTAL(9, range) which respects the filter. Fast for ad-hoc analysis; doesn't update when you change filters.
More on SUM & aggregation
See all →Reference
SUMPRODUCT
Multiply ranges together row by row and sum the result — ideal for weighted totals.
How-toHow to sum a column
Add up an entire column with SUM. Total a whole column or a fixed range — identical in Excel and Google Sheets.
Error fixFix 0
SUM returns 0 or the wrong total in Excel and Google Sheets — fix numbers stored as text, filtered ranges, manual calculation mode, and circular references.
ReferenceSUBTOTAL
Aggregate a range (sum, average, count…) while ignoring other SUBTOTALs and, optionally, hidden rows. Ideal under filters.
How-toHow to create a running total
Build a cumulative running total with a SUM and a clever anchored reference that grows as you copy it down. Works in both apps.
How-toHow to sum values by month
Total amounts that fall in a given month with SUMIFS and two date bounds. Works the same in Excel and Google Sheets.