Answer

To sum by color in Excel, direct built-in aggregation does not exist for cell fill or font color. Excel performs color-based operations through filtering, helper functions, or VBA. The method depends on the workflow requirements and the version of Excel. Windows and Mac support the same features for filtering and VBA, although Mac uses a different VBA editor layout.

Method 1: Sum by Color Using Filter and SUBTOTAL

Excel calculates values in visible cells through SUBTOTAL, which supports filtered views.

Steps

1. Select the header row.

2. Open the Data tab and choose Filter.

3. Click the filter arrow on the column with colored cells.

4. Choose Filter by Color and pick the color to sum.

5. Enter a SUBTOTAL function below the column:

   =SUBTOTAL(109, B2:B200)

   Code 109 sums only visible cells.

Key Points

• Excel hides non-matching colors during the filter, so SUBTOTAL processes only rows with the selected color.

• This method reacts dynamically when filters change.

• Windows and Mac follow identical menu paths for this feature.

Method 2: Sum by Color Using a GET.CELL Helper Function

Legacy Excel macros can evaluate cell color indexes. A named range calls the GET.CELL function, which extracts the color code.

Steps

1. Open Formulas → Name Manager → New.

2. Enter a name such as CellColorCode.

3. In Refers to, enter:

   =GET.CELL(63, Sheet1!B2)

4. Confirm the named range.

5. Enter =CellColorCode in a helper column next to each target cell and fill down.

6. Sum the values with a standard SUMIF:

   =SUMIF(C2:C200, 6, B2:B200)

   The number 6 corresponds to the color index extracted.

Key Points

• The GET.CELL method updates when the workbook recalculates.

• Recalculation requires Ctrl+Alt+F9 on Windows.

• Mac recalculates automatically through Command+=.

Method 3: Sum by Color with VBA

VBA retrieves color indexes directly. Excel identifies the color through.Interior.Color. The function returns totals for each color variant.

Steps

1. Press Alt+F11 on Windows or Option+F11 on Mac to open the VBA editor.

2. Insert a new module.

3. Paste the function:

   Function SumByColor(TargetRange As Range, ColorCell As Range) As Double

       Dim cell As Range

       Dim total As Double

       For Each cell In TargetRange

           If cell.Interior.Color = ColorCell.Interior.Color Then

               total = total + cell.Value

           End If

       Next cell

       SumByColor = total

   End Function

4. Return to Excel.

5. Enter a formula in a normal cell:

   =SumByColor(B2:B200, E1)

   E1 contains the example color.

Key Points

• The function evaluates the exact RGB color value.

• Changes in color do not trigger automatic recalculation.

• Press F9 on Windows or Command+= on Mac to refresh.

Choosing the Best Method

1. SUBTOTAL + Filter delivers the simplest no-code workflow. It updates when filters switch.

2. GET.CELL works without VBA and supports SUMIF logic.

3. VBA evaluates precise color values and supports large datasets.

All three methods apply to fill colors, highlight patterns, or conditional formatting results, although conditional formatting colors require rule-based summing instead of color-based logic.

If you want, you can request an optimized version for conditional formatting, dynamic color groups, or a dashboard-ready layout.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report