Skip to content
Guides
Sheets Mega ToolsGoogle Workspace add-on

Sum by Color in Google Sheets™

Sum, count, and average numbers grouped by cell background or text color in Google Sheets™, and write the results to your sheet.

Sum by Color totals the numbers in a range, grouped by cell background color or text color. Results appear in the sidebar, and you can write them to your sheet.

How to use Sum by Color#

  1. Select the range on your sheet

Highlight the cells you want to total. The tool has no range box on its main screen. It uses your current selection.

  1. Open the tool

Open the sidebar from Extensions > Text To Table Converter > ⚡ Launch Sidebar. On the home screen, under Sheets tools, choose Sheets Mega Tools, then choose Sum by Color.

You can also use Extensions > Text To Table Converter > Sheets Mega Tools to open the Sheets Mega Tools screen directly.

  1. Choose the color basis
    • Background (default)
    • Text
  1. Click Calculate

If the range has more than 10,000 cells, a warning appears first. You can continue or cancel. A range over 100,000 cells stops with an error.

  1. Review the results

The list shows one row per color group with a color swatch and these columns:

  • Sum: the total of the numeric cells in that group
  • Count: the number of cells in that group
  • Average: the average of the numeric cells in that group

Groups are ordered by sum, highest first.

  1. Write the results to your sheet (optional)

After results appear, a Target range box and a Use selection button appear. Type a range such as D1 or click Use selection after selecting a spot on the sheet, then click Write to sheet. The table is written with a color swatch in the first column.

How it works#

  • Only numeric cells are added into the sum and average. Text and other non-numeric cells are counted in Count but not summed.
  • Cells with no fill color are counted in the white (#ffffff) group. There is no separate blank group.
  • Colors that are the same shade are grouped together.

Use Cases#

  • Add up expenses by category color
  • Total values for status colors, such as red for urgent and green for done
  • Check totals for data that was color-coded by hand

Example#

A range contains:

  • Red cells: 100, 200, 150
  • Green cells: 50, 75, 25
  • Blue cells: 300, 400

The results show:

  • Blue: Sum 700, Count 2, Average 350
  • Red: Sum 450, Count 3, Average 150
  • Green: Sum 150, Count 3, Average 50

Note: This tool is part of Sheets Mega Tools, available only in Google Sheets™.