How to sum cells by color in Google Sheets
By Jake Marsh · Updated
To sum cells by color in Google Sheets, install Huecount and type =SUMBYCOLOR(range, color), for example =SUMBYCOLOR(C2:C90, E1) to add every number in C2:C90 whose fill matches cell E1. Sheets has no built-in function for this, because SUMIF only reads values. When the color is in one column and the amounts are in another, add a third argument: =SUMBYCOLOR(A2:A90, E1, C2:C90) adds column C wherever column A has that color.
Steps
- Install Huecount from the Google Workspace Marketplace and open your spreadsheet.
- Put a sample of each color in a cell off to the side, such as E1 for green and E2 for yellow. Pointing at a sample is easier than typing color codes, and the total follows if you recolor the sample.
- Click the cell for the total and type the formula:
| You want | Formula |
|---|---|
| Sum the colored cells themselves | =SUMBYCOLOR(C2:C90, E1) |
| Sum column C where column A is colored | =SUMBYCOLOR(A2:A90, E1, C2:C90) |
| Sum two colors together | =SUMBYCOLOR(A2:A90, "red|yellow", C2:C90) |
| Sum everything that has any fill | =SUMBYCOLOR(A2:A90, "not white", C2:C90) |
| Sum by text color | =SUMBYFONTCOLOR(C2:C90, E1) |
- Drag the formula down next to E2, E3 and so on. The references adjust like any other formula, so one formula per color takes seconds.
- Open Extensions > Huecount > Open Huecount and tick Recalculate automatically when colors change, so the totals move when someone recolors a row.
Let the sidebar write it
If you'd rather not type, select the color column in the sheet, click Use selection and Count colors in the sidebar. Each color gets a line with its cell count, sum and average. Click a color, choose Sum, type the numbers range (for example C2:C90) and the cell to put the result in, and click Insert formula.
Example
A list of invoices with the customer in column A, filled green when paid and yellow when sent, and the amount in column C:
| A | C | |
|---|---|---|
| 2 | Harbor Supply (green) | 1,200 |
| 3 | North Fork Farm (yellow) | 450 |
| 4 | Bell Street Cafe (green) | 300 |
=SUMBYCOLOR(A2:A4, "green", C2:C4) returns 1,500 when green is the exact swatch the cells use, and =SUMBYCOLOR(A2:A4, E1, C2:C4) with a sample cell avoids having to know the swatch's name.
When the numbers don't add up
- The amounts range is a different size. SUMBYCOLOR returns an error saying the values range must be the same size as the color range, instead of adding the wrong cells. Make both ranges cover the same rows.
- Text that looks like a number. Only real numbers are added. Cells with numbers stored as text are skipped.
- A shade that's almost the same. Colors pasted from another file can be a shade off. Add the option
"tolerance=10"as the last argument. - Colors from rules. Rule colors are summed by default. Add
"nocf"to sum each cell's own fill instead.
The same arguments work for averages, minimums and maximums: AVERAGEBYCOLOR, MINBYCOLOR and MAXBYCOLOR.
What it costs
The trial lasts 14 days or 50 color breakdowns in the sidebar, whichever comes first, and the formulas work all through it. No card needed. After that it's $2.99 a month, $19 a year or $49 once for lifetime access per Google account, or $99 a year for a team plan that covers everyone at your company or school domain. Every plan and the install link are on the Huecount page.
Huecount: Count & Sum by Color
Count and sum cells by color in Google Sheets, with formulas that update when colors change $2.99 a month or $19 a year per Google account.
Questions
Can SUMIF sum by color in Google Sheets?
No. SUMIF compares values, and a cell's color isn't a value it can see. SUMBYCOLOR works like SUMIF with a color in place of the condition.
Can I sum one column based on the color of another column?
Yes. Give the colored column first and the numbers last: =SUMBYCOLOR(A2:A90, E1, C2:C90).
Will the total update when I change a color?
With Recalculate automatically when colors change turned on, yes, a few seconds after the change. Otherwise use Refresh color formulas now in the Huecount menu.
Can I sum cells on another tab?
Yes. Ranges like 'Q3 sales'!B2:B90, whole columns like B:B and named ranges all work.
More Huecount guides
- How to count cells by font color in Google Sheets
- How to count conditional formatting colors in Google Sheets
More Google guides
- How to make a Google Sheets spreadsheet accessible (Includewell)
- How to calculate driving distance in Google Sheets (Geocode & Map Addresses)
- How to print 5160 address labels from Google Sheets (Labelyard)
- How to match addresses in two lists in Google Sheets (Fuzzwell)