How to Sum Colored Cells in Excel? (4 Easy Methods)

Sharing is caring!

Color-coded data is a common way to make spreadsheets easier to read and analyze. But what happens when you want to sum values based on cell color? Excel doesn’t offer a built-in feature to do this directly, but there are several effective methods to sum colored cells using formulas, VBA, and even third-party tools.

In this guide, we will explain multiple ways to sum colored cells in Excel, including non-VBA methods and custom VBA solutions. Whether you’re managing budgets, tracking sales, or analyzing tasks, you’ll find the right method for your needs.

Why Sum Colored Cells in Excel?

Using colors in Excel often helps highlight important data, such as:

• Overdue tasks (highlighted in red)
• Completed items (green fill)
• Priority purchases (yellow background)

Instead of manually adding numbers, it’s much faster to sum values by color automatically. Excel doesn’t offer a direct “sum by color” button, but you can do it with a few workarounds.

Methods to Sum Colored Cells in Excel

There are four reliable methods to sum colored cells in Excel:

1. Using the SUBTOTAL function with filters
2. Creating a VBA custom function
3. Applying the GET.CELL formula (non-VBA)
4. Using a third-party Excel add-in

Let’s explore each one step by step.

1. Using SUBTOTAL Function with Filter (No VBA)

This is the easiest and fastest way to sum colored cells without coding. It uses Excel’s built-in filter feature along with the SUBTOTAL function.

Steps:

1. Select your data range and click Data > Filter.
2. Use the filter dropdown to choose a specific cell color.
3. In a separate cell, enter this formula: =SUBTOTAL(9, A2:A20)
   • Replace A2:A20 with your actual range.
   • The 9 tells Excel to perform a SUM of the visible cells only.

Example:

If only the yellow-colored rows are visible, SUBTOTAL will sum only those prices.

Pros:

• No coding required
• Automatically updates when filters are changed

Cons:

• Works only with filtered cells
• Doesn’t permanently identify the color for future use

2. Using VBA Custom Function to Sum by Color

If you want a more flexible and reusable method, use VBA (Visual Basic for Applications) to create your own user-defined function.

Steps:

1. Press Alt + F11 to open the VBA editor.
2. Go to Insert > Module.
3. Paste the following code:

Function SumByColor(SumRange As Range, SumColor As Range) As Double
    Dim SumColorValue As Integer
    Dim TotalSum As Double
    Dim rCell As Range
    SumColorValue = SumColor.Interior.ColorIndex
    For Each rCell In SumRange
        If rCell.Interior.ColorIndex = SumColorValue Then
            TotalSum = TotalSum + rCell.Value
        End If
    Next rCell
    SumByColor = TotalSum
End Function

4. Close the VBA editor.

5. Use the new function in a cell:
=SumByColor(B2:B20, D1)

B2:B20 is the range to sum.

D1 is a reference to a cell with the target color.

Example:

If D1 is a green cell, and you use the function on B2:B20, it will sum only the values in green-colored cells.

Pros:

• Customizable to your needs
• Can be reused across workbooks

Cons:

• Requires VBA knowledge
• Might need permission settings in corporate environments

3. Using GET.CELL Formula (Non-VBA Method)

This method uses Excel’s GET.CELL macro function, which is part of Excel 4.0 macros. It works by returning the color code of a cell. Combined with helper columns and SUMIF, this is a powerful non-VBA technique.

Step 1: Define a Named Range

1. Go to Formulas > Name Manager > New.
2. Name it CellColor.
3. In the Refers to box, enter: =GET.CELL(38, A2)
   • Replace A2 with the first cell in your data range.
   • 38 returns the fill color code.

Step 2: Add a Helper Column

Next to your dataset, create a column that uses the named formula:

=CellColor

Drag this down to get the color code for each cell.

Step 3: Use SUMIF to Sum by Color Code

Now apply the formula:

=SUMIF(D2:D20, 6, B2:B20)

• D2:D20: Column with color codes
• 6: Color code for the target color (you can reference it from another cell)
• B2:B20: Range to sum

Pros:

• No macros or VBA
• Works with any color format

Cons:

• Requires helper columns
• Color code values might differ between systems

4. Using Third-Party Add-Ins

If you’re not comfortable with formulas or VBA, Excel add-ins can make this task very simple. One popular tool is Ablebits Count & Sum by Color.

How It Works:

1. Install the add-in from Ablebits.
2. Select the range you want to analyze.
3. Choose the color to sum.
4. Instantly see totals, counts, averages, etc.

Pros:

• User-friendly interface
• No formulas or scripting
• Supports background and font colors

Cons:

• Not free (trial available)
• Add-in must be installed on every device

Comparison of All Methods to Sum Colored Cells in Excel

Here’s a side-by-side comparison to help you choose the best approach:

When Should You Use Each Method?

Tips for Working with Colored Cells in Excel

• Use consistent fill colors throughout your sheet.
• Test color codes using helper columns to avoid mismatches.
• Use conditional formatting for more consistent styling (though it might affect color index detection).
• Save your work before trying VBA scripts or third-party add-ins.
• If sharing files, remember that VBA functions and add-ins might not work on another user’s device unless enabled.

Example Use Case: Budget Tracking

Let’s say you’re tracking monthly expenses. You color-code rows like this:

• Green: Essentials
• Yellow: Optional
• Red: Overspending

To get the total amount spent on essentials, you can:

• Apply a green filter and use SUBTOTAL.
• Use the SumByColor VBA function with a green reference cell.
• Use GET.CELL to extract color codes and SUMIF the matching rows.

This helps with financial analysis, showing how much you’re spending in each category—without needing to add labels or change your spreadsheet structure.

Final Thoughts

Although Excel doesn’t have a built-in way to sum colored cells, you can still do it using:

• Filters and SUBTOTAL
• Custom VBA
• GET.CELL helper columns
• Third-party Excel tools

Each method has its strengths. Choose the one that fits your needs and comfort level. By learning how to sum by color, you can make your data analysis faster, clearer, and more meaningful—especially in visual dashboards, task trackers, and financial spreadsheets.

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.