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.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Sum by Category in Excel
How to Sum by Year in Excel
How to Sum by Month in Excel
How to Sum by Week in Excel
The sum cells by color was great and solved my problem. However, it does not update as colors change – only way I could find is to enter the formula and press enter – is it possible to update the result automatically? Maybe a macro? I tried one but the formula is being used in many cells.
Hello,
The code works great although the dataset I am working with is Decimal.
I cant seem to use Decimal instead of Integer, is there a way around this?
Hi Jude…To sum by color in Excel using VBA and handle decimal values instead of integers, you need to adjust your VBA code slightly to accommodate decimal (or floating-point) numbers. By default, VBA uses `Integer` and `Long` types for whole numbers, but for decimals, you can use the `Double` or `Currency` data type to handle precision with decimal places.
Here’s how you can modify your VBA code to handle decimal numbers:
### 1. Open VBA Editor
– Press `Alt + F11` to open the VBA editor.
– In the editor, go to `Insert > Module` to add a new module.
### 2. Use `Double` instead of `Integer` for Decimals
You’ll need to adjust your VBA code to ensure that the variables storing the sum can handle decimal values. Here’s an example of how you can modify the code to use `Double` instead of `Integer`:
“`vba
Function SumByColor(rng As Range, color As Range) As Double
Dim cell As Range
Dim total As Double ‘ Use Double for decimal values
Dim colorIndex As Integer
colorIndex = color.Interior.ColorIndex
For Each cell In rng
If cell.Interior.ColorIndex = colorIndex Then
If IsNumeric(cell.Value) Then
total = total + cell.Value ‘ Handles decimal values
End If
End If
Next cell
SumByColor = total
End Function
“`
### Explanation:
– `Dim total As Double`: This ensures that the `total` variable can hold decimal values.
– The `If IsNumeric(cell.Value)` condition checks if the cell contains a numeric value before adding it to the sum.
### 3. Save and Exit VBA
– After editing the code, close the VBA editor (`Alt + Q`) and return to Excel.
### 4. Use the Function in Excel
You can now use the `SumByColor` function in Excel as you normally would:
“`excel
=SumByColor(A1:A10, B1)
“`
Where `A1:A10` is the range of numbers (which can include decimals) and `B1` is the cell with the color you want to sum by.
This should allow you to sum decimal values by color without any issues.