Aller au contenu
Convertisseur Texte en Tableau

Sum by Color

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

Section intitulée « How to Use Sum by Color »

  1. Open the Sum by Color feature Navigate through the Google Workspace™ menu: Extensions > Text To Table Converter > Mega Tools > Sum by Color.

  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

Section intitulée « 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

Section intitulée « 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

Section intitulée « 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