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) and manually type the color label — "Red", "Blue", etc. — for each row to mirror the fill colors you applied.
- 2In an empty cell enter =SUMIF(B2:B5,"Red",C2:C5) to sum column C values where the helper column equals "Red".
- 3Alternatively in Excel, open the VBA editor (Alt+F11), create a function that reads Interior.Color and use it as =SumByColor("Red",C2:C5). In Google Sheets use Apps Script for the same purpose.
Tips
- The helper-column approach is the most stable — it works in every version, survives copy-paste, and does not break when color themes change.
- VBA/Apps Script color-sum functions do NOT recalculate automatically when colors change; you must force a recalculation (press F9 in Excel, or re-run the script in Sheets).
- If you need color-based aggregation regularly, consider switching to a data table with a categorical label column instead.
Need it for your exact data?
Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.
Frequently asked
Is there a native SUMBYCOLOR function in Excel or Sheets?
No. Neither application exposes cell fill color to worksheet formulas natively. You must use a helper column, VBA, or Apps Script.
Will a VBA sum-by-color function work in Google Sheets?
No — VBA is Excel-only. Use Google Apps Script with getBackground() to build an equivalent custom function in Sheets.
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.