Function Problems

How to Sum Excel Cells by Fill Color: 3 Proven Methods

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs a way to calculate the sum of values in Excel cells that share a specific background fill color.

Product: Excel
Device & OS: not provided
Scenario: Calculating totals for data that has been manually categorized or highlighted using background cell colors.
Observed behavior: Excel does not have a native, standard worksheet function that directly sums cells based on their formatting or fill color.

Before you start

Determine whether your cell colors were applied manually or through conditional formatting, as this will help you choose the most effective calculation method.

Solution 1Recommended

Use Filter by Color and the SUBTOTAL Function

The easiest method without using code. By filtering your dataset by cell color, you can use the SUBTOTAL function to sum only the visible rows.

The SUBTOTAL function with the function number 9 is designed to sum values while ignoring rows that have been hidden by a filter. This makes it a perfect workaround for summing colored cells without needing advanced programming.

Apply a filter to your data

Select your entire dataset, including headers. Navigate to the 'Data' tab on the ribbon and click the 'Filter' button.

Filter the column by color

Click the filter dropdown arrow on the header of the column containing the colored cells. Hover over 'Filter by Color' and select the specific background color you want to sum.

Enter the SUBTOTAL formula

In a blank cell below your data, type the formula =SUBTOTAL(9, range), replacing 'range' with the cells containing your numbers (e.g., =SUBTOTAL(9, B2:B100)). Press Enter to see the sum of only the colored, visible cells.

Updating the Sum: If you change a cell's color later, the SUBTOTAL formula won't update automatically. You must reapply the color filter to refresh the visible rows.

Solution 2

Create a Custom VBA User-Defined Function

Best for recurring tasks. You can create a custom VBA function that checks the background color of cells and sums the matching values automatically.

Solution 3

Sum Using the Underlying Conditions

If your cells are colored based on specific data rules or status indicators, you can sum them by evaluating those underlying rules instead of the color itself.

Efficient Spreadsheet Management

Filter and Sum Colored Cells Effortlessly in WPS Spreadsheet

WPS Spreadsheet fully supports advanced filtering, the SUBTOTAL function, and logical formulas like SUMIF. You can quickly categorize, filter, and calculate colored data without missing a beat.

1. Select your data: Open your workbook in WPS Spreadsheet, highlight your data, and click 'Filter' under the Data tab.
2. Filter by background color: Click the filter icon on your column header, choose 'Filter by Color', and pick the specific fill color.
3. Calculate the visible sum: Use the =SUBTOTAL(9, your_range) formula at the bottom of your dataset to instantly sum the colored cells.

Free, lightweight, and fast spreadsheet processingFully compatible with Microsoft Excel (.xlsx) formulas and formattingBuilt-in robust 'Filter by Color' capabilitiesFamiliar user interface requiring zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Does the SUMIF function work with cell fill colors?

No, the standard SUMIF and SUMIFS functions evaluate cell values and logical criteria, not formatting or background colors. To sum by color, you must use a filter with SUBTOTAL, a custom VBA function, or evaluate the condition that caused the color.

Will my SUBTOTAL formula update automatically if I change a cell's color?

No. Changing a cell's background color does not trigger Excel to recalculate formulas or update filters automatically. If you change a color, you will need to reapply the color filter to update the visible rows and the resulting SUBTOTAL calculation.

Can I use VBA to sum cells colored by Conditional Formatting?

Reading conditionally formatted colors via VBA is quite complex because the standard 'Interior.Color' property only returns manually applied colors. For conditionally formatted cells, it is much easier to use a SUMIFS formula that mimics the exact logical rules used in your conditional formatting.

Excel & Data Problems