How to Sum Cells By Color in Excel

While not a built-in Excel feature, we can create our own function to easily sum cells by color.

Contents:

1. How to Use VBA User Defined Functions

2. How to Sum All Colored Cells

3. How to Sum Cells By Specific Cell Color

How to Use User Defined Functions

The two examples below will use a "User Defined Function". Just like how SUM and COUNT are considered functions, we can create our own functions using VBA.

This is the same process as using a macro, but instead of running a macro, we can instead call our function similar to how the built in functions are called in a formula (by typing "=" followed by the function name).

The first step to using a user defined function is to take the snippet of VBA code, and place that into your workbook. To do this, open up the VBA editor by pressing alt + F11 or going to the developer tab > Visual Basic:

Next, we'll need to create a module to place the custom functions in. On the left menu, right click on your workbook name, and select insert > Module. Any code entered in to this module will be available for use anywhere in the workbook.

Then all you have to do is drop the Function code into the blank area to the right of that menu, and you'll be able to call in your custom function by its name in any cell in your workbook.

How to Sum All Colored Cells in a Range

To sum all colored cells in a range, or any cell in a given range that does not have a blank background, we can use a user defined function we'll call "SumAllColoredCells".

This function will search a given range for any colored cells and sum them.

Function SumAllColoredCells(eval_range As Range) As Double
    Dim cell As Range
    Dim total_sum As Double

    totalSum = 0

    For Each cell In eval_range
        If cell.Interior.ColorIndex <> xlNone Then
            If IsNumeric(cell.Value) Then
                total_sum = total_sum + cell.Value
            End If
        End If
    Next cell

    SumAllColoredCells = total_sum
End Function

For example, here we have a workbook with a few different colors denoting different results. If we want to know how many cells in this range are colored, we can use this formula (after we've dropped the function into a module):

= SumAllColoredCells(cell_range)

= SumAllColoredCells(B3:B12)

By using the "SumAllColoredCells" function and selecting the range we want to sum, we can return the sum of all colored cells (ignoring blank cells) using our custom function.

How to Sum Cells in a Range Matching a Specific Color

Instead of summing all colored cells in a range, what about summing cells in a range that match a specific color?

For example, we can use the same example above, but create a SUMIF of sorts where our formula will sum cells with a background color that matches a reference cell color.

Function SumByColor(eval_range As Range, cell_reference As Range) As Double
    Dim cell As Range
    Dim reference_color As Long
    Dim total_sum As Long

    reference_color = cell_reference.Interior.Color
    total_sum = 0

    For Each cell In eval_range
        If cell.Interior.Color = reference_color Then
            If IsNumeric(cell.Value) Then
                total_sum = total_sum + cell.Value
            End If
        End If
    Next cell

    SumByColor = total_sum
End Function

To use this function, we will be using a slightly different structure, by defining the range to evaluate at as well as a cell containing the color that we want to sum.

= SumByColor(cell_range, reference_cell)

= SumByColor(B3:B12,D3)

Here our function is looking at the color of D3, and summing cells which match that color in the range B3:B12. By copying this formula down, we can look at each the sum of each individual color, green, yellow and red.