How to sum by color in Excel?

---

Often you may want to sum values in Excel based on their color.

For example, suppose we have the following dataset and we’d like to sum the values in the cells based on the cell colors:

The easiest way to do this is by writing some code in VBA in Excel.

This might seem intimidating if you’re not familiar with VBA but the process is straightforward and the following step-by-step example shows exactly how to do so.

Step 1: Enter the Data

First, enter the data values into Excel:

Step 2: Show the Developer Tab in Excel

Next, we need to make sure the Developer tab is visible on the top ribbon in Excel.

To do so, click the File tab, then click Options, then click Customize Ribbon.

Under the section called Main Tabs, check the box next to Developer, then click OK:

Step 3: Create a Macro Using VBA

Next, click the Developer tab along the top ribbon and then click the Visual Basic icon:

Next, click the Insert tab and then click Module from the dropdown menu:

Next, paste the following code into the module code editor:

Function SumCellsByColor(CellRange As Range, CellColor As Range)

Dim CellColorValue As Integer
Dim RunningSum As Long

CellColorValue = CellColor.Interior.ColorIndex
Set i = CellRange

For Each i In CellRange
    If i.Interior.ColorIndex = CellColorValue Then
    RunningSum = RunningSum + i.Value
    End If
Next i

SumCellsByColor = RunningSum

End Function

The following screenshot shows how to do so:

Next, close the VB Editor.

Step 4: Use the Macro to Sum Cells by Color

Lastly, we can use the macro we created to sum the cells based on color.

First, fill in cells C2:C4 with the colors that you’d like to sum.

Then type the following formula into cell D2:

=SumCellsByColor($A$2:$A$11, C2)

Drag and fill this formula down to each remaining cell in column D and the formula will automatically sum each of the cells that have specific background colors:

For example, we can see that the sum of the cells with a light green background is 53.

We can confirm this by manually calculating the sum of each cell with a light green background:

Sum of Cells with Light Green Background: 20 + 13 + 20 = 53.

This matches the value calculated by our formula.