Sum by Color in Google Sheets™

The Sum by Color tool computes sums grouped by color (background or text) for a single range. Results are displayed in the sidebar and can optionally be written to your sheet.

How to Use Sum by Color

1. Open the tool

Open the add-on sidebar, expand Sheets Mega Tools, go to Cell Values, and click Sum by Color.

You can also use the Sheets Mega Tools menu shortcut to open the same sidebar section.

2. Select your range

Use the range picker:

• Editable range input - Manually enter range in A1 notation (e.g., A1:B10 or Sheet1!A1:B10)
• Current selection button - Click the icon button to pull your current selection (shows spinner while fetching)

3. Choose color basis

Select what to analyze via radio buttons:

• Background (default) - Calculate sums based on cell background colors
• Text - Calculate sums based on cell text colors

4. Calculate sums

Click the "Calculate" button to compute sums by color.

5. Review results in sidebar

The sidebar displays a results table with:

• Color swatch - Visual representation of each color
• Sum - Sum of numeric values for that color
• Count - Total number of cells with that color
• Average - Average of numeric values for that color
• Results sorted by sum (descending), then by color hex

6. Write results to sheet (optional)

Click the "Write to sheet" button and:

• Enter target range in A1 notation (e.g., D1:F10)
• Results table is written with proper formatting:
• Color swatches in the first column
• Formatted numeric columns for sum, count, and average

How It Works

• Non-numeric cells are ignored for sum/average calculations
• Only numeric cells are counted in the sum (tracked separately as numericCount)
• Colors are keyed by hex (lowercase, normalized)
• Blank cells are grouped separately
• Results include:
• colorHex - The color value
• sum - Sum of numeric values
• count - Total cells with that color
• numericCount - Numeric cells used in sum
• average - Calculated from numeric cells only

Use Cases

• Budget analysis - Sum expenses by category color
• Status tracking - Calculate totals for different status colors (e.g., red = urgent, green = complete)
• Category totals - Sum values grouped by color-coded categories
• Data validation - Verify totals for manually color-coded data
• Quick calculations - Get sums without writing complex formulas

Example

If you have a spreadsheet with:

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

The results would show:

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