VBA & Macro Problems

How to Sum Excel Values Based on Cell Background Color

Guest Writer Oct 1, 2026 868 views

Question details

The user needs a method to calculate the total sum of cell values that share a specific background color, particularly for calculating totals in a colored scorecard.

How to Sum Excel Values Based on Cell Background Color

Product: Excel
Device & OS: not provided
Scenario: Creating a scorecard where cells change color (e.g., turning red or green) when selected. The user wants Excel to observe these colors and automatically sum the scores represented by the colored cells on another worksheet.
Observed behavior: Excel does not have a standard built-in worksheet function that can sum or calculate values based purely on a cell's background color or formatting.

Before you start

Since standard Excel functions cannot read cell colors, you will need to use VBA (Visual Basic for Applications). Make sure your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that macros are permitted in your security settings.

Solution 1Recommended

Use a Custom VBA Function (User-Defined Function)

Create a custom VBA function to read the background color index of a reference cell and sum all matching cells in a specified range.

By writing a simple User-Defined Function (UDF) in VBA, you can create a custom formula like =SumByColor(). This function compares the Interior.ColorIndex of each cell in your target range to a reference cell and tallies the corresponding values.

Open the VBA Editor

Press Alt + F11 on your keyboard to open the Microsoft Visual Basic for Applications window.

Insert a New Module

Click on 'Insert' in the top menu and select 'Module'. This will create a blank code window.

Paste the VBA Code

Copy and paste the following code into the module: Function SumByColor(CellColor As Range, SumRange As Range) Dim c As Range Dim ColorIndex As Integer Dim Total As Double ColorIndex = CellColor.Interior.ColorIndex For Each c In SumRange If c.Interior.ColorIndex = ColorIndex Then Total = Total + c.Value End If Next c SumByColor = Total End Function

Apply the Formula in Your Worksheet

Close the VBA editor. In your scorecard, type =SumByColor(A1, C4:E10) where 'A1' is a cell with the exact background color you want to match, and 'C4:E10' is the range you want to sum.

Use a Custom VBA Function (User-Defined Function)

Manual Recalculation Required: Changing a cell's background color does not trigger Excel to automatically recalculate formulas. You must press F9 or edit a cell's content to update the SumByColor result.

Solution 2

Use SUMIF with Helper Columns (Scorecard Best Practice)

Instead of relying on cell formatting, use a data-driven approach with SUMIF or SUMIFS. This is much more reliable for scorecards and does not require macros.

WPS Spreadsheet Alternative

Use WPS Spreadsheet to Sum Cells by Color

WPS Office Spreadsheet provides a seamless environment for data analysis and scorecard creation. It fully supports VBA macros and advanced conditional formatting, allowing you to execute the SumByColor code exactly as you would in Microsoft Excel.

1. Open Your Workbook: Launch WPS Office and open your scorecard spreadsheet.
2. Access the Developer Tools: Navigate to the Developer tab on the ribbon and click on the 'VBA Editor' icon.
3. Insert and Run the Macro: Insert a new module, paste the SumByColor function code, and save the workbook.
4. Use the Custom Formula: Type =SumByColor() directly into your spreadsheet cells to tally up your colored scores.

Fully compatible with Microsoft Excel (.xlsx and .xlsm) macro-enabled formats.Built-in Developer tools to easily write and run VBA scripts.Robust Conditional Formatting and SUMIF capabilities for complex scorecards.Free, lightweight, and user-friendly interface.

microsoft office alternative - wps office

Frequently Asked Questions

Will the SumByColor VBA function update automatically if I change a cell color?

No. Excel does not recognize formatting changes as a trigger for calculation. To update the sum after changing a cell's background color, you must force a recalculation by pressing the F9 key.

Why doesn't the VBA function work on cells colored by Conditional Formatting?

The standard VBA property (Interior.ColorIndex) only reads manually applied cell colors. It cannot read colors dynamically applied by Conditional Formatting. To sum conditionally formatted cells, you should use SUMIFS based on the same logic that drives your Conditional Formatting rules.

Is there a way to sum by color without using VBA?

Without VBA, you can apply a Data Filter to your columns, choose 'Filter by Color', and highlight the visible numbers to see their sum in the bottom Status Bar. Alternatively, you can use the SUBTOTAL(109, range) function, which will only sum the visible rows after filtering by color.

Excel & Data Problems