Google Sheets has no built-in way to sum or count cells by their color. Formulas like SUMIF only look at values, not at background or font colors. Color Sum is a small Google Sheets add-on that fixes this: it adds SUMBYCOLOR, COUNTBYCOLOR and AVERAGEBYCOLOR functions and a sidebar that totals colored cells in one click.
How to sum cells by color in Google Sheets
- Install Color Sum from the Google Workspace Marketplace (Extensions > Add-ons > Get add-ons, search “Color Sum”).
- Color a cell with the color you want to match, for example D1.
- Type a formula into any cell:
=SUMBYCOLOR("B2:B100", "D1")
=COUNTBYCOLOR("B2:B100", "D1")
=AVERAGEBYCOLOR("B2:B100", "D1")
=SUMBYFONTCOLOR("B2:B100", "D1")
=COUNTBYFONTCOLOR("B2:B100", "D1")
The second argument can also be a color code such as "#ffff00". Put the range in quotes.
Example
| Task | Amount |
|---|---|
| Invoice 101 | 120 |
| Invoice 102 | 80 |
| Invoice 103 | 200 |
| =SUMBYCOLOR(„B2:B4“, „B2“) | 320 (green only) |
Sidebar: totals without formulas
Open Extensions > Color Sum > Sum & count by color, pick a cell with the color you want and the sidebar shows the sum, count and average of matching cells. Pro adds font colors and a summary table with totals for every color in the range.
Good to know
- Google Sheets does not recalculate formulas when you only change a color. Edit any cell or click Recalculate in the sidebar.
- Works with background colors and font colors.
- Privacy: the add-on only accesses the spreadsheet you open it in. Your data never leaves Google Sheets.
Free vs Pro
All formulas and the background-color sidebar are free. Pro adds font colors in the sidebar and the all-colors summary table, for a one-time payment – no subscription: get Color Sum Pro.
FAQ
Can SUMIF sum by color? No. SUMIF and SUMIFS only check cell values. You need a custom function such as SUMBYCOLOR.
Does it work on conditional formatting colors? It reads the color stored on the cell. Colors produced only by conditional formatting rules are not visible to Apps Script, so color those cells manually or use the rule’s condition in SUMIF instead.
Is it safe? Yes – it uses the minimal “current spreadsheet only” permission. Privacy policy · Terms
