How to sum cells by color in Google Sheets

Updated November 7, 2026

Google Sheets can add numbers with SUM and SUMIF, but there is no built-in function to sum cells by their fill color. You can filter by color and use SUBTOTAL, write a small Apps Script function, or use an add-on with a SUMBYCOLOR formula. Here is how each works.

Method 1: Filter by color and SUBTOTAL (no add-on)

1. Click a cell in your data and choose Data → Create a filter.
2. In the column header, click the filter icon, then Filter by color → Fill color, and pick the color.
3. In a cell outside the filtered rows, type:

   =SUBTOTAL(109, B2:B50)

109 sums only the visible rows. Use 101 for the average. You see one color at a time, and the total changes whenever the filter changes, so this is best for a quick check, not for a report.

Method 2: Apps Script custom function

Open Extensions → Apps Script, paste this and click Save:

function sumColoredCells(rangeA1, color) {
  var range = SpreadsheetApp.getActiveSpreadsheet().getRange(rangeA1);
  var colors = range.getBackgrounds();
  var values = range.getValues();
  var target = color.toLowerCase();
  var sum = 0;
  for (var r = 0; r < colors.length; r++) {
    for (var c = 0; c < colors[r].length; c++) {
      if (colors[r][c].toLowerCase() === target && typeof values[r][c] === 'number') {
        sum += values[r][c];
      }
    }
  }
  return sum;
}

=sumColoredCells("B2:B50", "#00ff00")

It works, with the same catches as any color script: the range sits in quotes and does not move when you insert rows, you need the exact color code, and the total does not update when you recolor a cell.

Method 3: SUMBYCOLOR and AVERAGEBYCOLOR with Cellhue

With the Cellhue add-on installed, sum and average by color with normal ranges:

=SUMBYCOLOR(B2:B50, "#00ff00")
=AVERAGEBYCOLOR(B2:B50, "#00ff00")
=SUMBYCOLOR(B2:F100, H1)

• The color can be a code like "#00ff00" or "#0f0", or a sample cell like H1.
• Only numbers are added. Text and empty cells in that color are skipped.
• If no cell of that color holds a number, AVERAGEBYCOLOR shows a message instead of a division error.
• The sidebar (Extensions → Cellhue → Open sidebar, then Analyze selection) lists every color in a range with its sum, count and average, so you can see the color codes and insert a formula in one click.

Example: a budget with colored rows

Expenses in B2:B40, paid ones filled green, unpaid ones red:

Paid:    =SUMBYCOLOR(B2:B40, "#d9ead3")
Unpaid:  =SUMBYCOLOR(B2:B40, "#f4cccc")
Unpaid items: =COUNTBYCOLOR(B2:B40, "#f4cccc")

Mark a row green and the totals follow after a refresh.

Why color totals go stale

Google Sheets recalculates formulas when values change. Changing only a color is not such a change, so any color formula, from a script or an add-on, can keep showing the old total. Cellhue refreshes its formulas when you open the file and when you click Extensions → Cellhue → Refresh all. With Pro, it watches the colors while the sidebar is open and recalculates by itself.

Common questions

Can SUMIF sum by color?

No. SUMIF and SUMIFS compare values, not formatting.

Does it work with colors from conditional formatting?

No, only with colors set on the cell. For rule-based colors, use SUMIF with the same condition as the rule.

Is there a size limit?

The Free plan handles ranges up to 1,000 cells. Pro removes the limit.

Need a count instead of a sum? See how to count colored cells.