# How to Sum Colored Cells in Excel (4 Ways)

There’re no built-in Excel functions that sum up the colored cells in Excel by themselves. However, there are a few ways to implement this kind of calculation. Here’s an overview of the functions we’ll use.

We will be using a Product Price List data table.

## Method 1 – Using the Excel SUMIF Function to Sum Colored Cells

We want to sum up the total price of the products having “MTT” in their product IDs. We marked them with a blue color.

Steps:

• Add an extra column to specify the cell colors in column Price.
• Select cell C16.
• Insert the following formula:
=SUMIF(E5:E13,"Blue",D5:D13)
• Press the Enter button.

## Method 2 – Using AutoFilter and SUBTOTAL to Add Colored Cells

Steps:

• Select the whole data table.
• Go to the Data tab.
• Click on the Filter command.

• Click on the dropdown icon at the corner of the Price column header.
• From the dropdown menu, select Filter by Color.
• Click on the blue color rectangle.

• Select cell C16 and insert the following formula:

=SUBTOTAL(109,D5:D7)

• Press Enter.

## Method 3 – Using the GET.CELL Function to Sum Colored Cells

Steps:

• Go to Formulas, choose Defined Names, and select Name Manager.

• The Name Manager dialog box will pop up.
• Click on New.

• The Edit Name dialog box will pop up.
• Assign a name, for example Code, within the Name bar.
• Insert the following code within the Refers to the bar.
=GET.CELL(38,\$D5)
• Hit the OK button.

• Create a new column.
• Select cell E5 and insert:
=Code

• Press the Enter button.

• Drag the Fill Handle icon to the end of the Code column.

• Select cell C16 and enter the formula:
=SUMIF(E5:E13,33,D5:D13)
• Hit Enter.

␥  Formula Breakdown

• =GET.CELL(38,GET.CELL!\$D5) ▶ 38 refers to the sum operation; GET.CELL! refers to the sheet name; \$D5 is the cell address of the first colored cell.
• =Code ▶ it’s a synthesized code created in step 7.
• =SUMIF(E5:E13,33,D5:D13) ▶ sums up the values of the cells in the Price column having color code 33.

## Method 4 – Using Excel VBA to Add Colored Cells

• Press Alt + F11 button to open the Excel VBA window.

• Go to Insert and select Module.

• Copy the following VBA code.
Function SumColoredCells(CC As Range, RR As Range)
Dim X As Long
Dim Y As Integer
Y = CC.Interior.ColorIndex
For Each i In RR
If i.Interior.ColorIndex = Y Then
X = WorksheetFunction.Sum(i, X)
End If
Next i
SumColoredCells = X
End Function
• Paste and save the code in the VBA editor.

• Select cell D16.
• Insert the following formula:
=SumColoredCells(\$D\$5,D5:D13)

• Hit Enter.

␥  Formula Breakdown

Syntax =SumColoredCells(colored_cell,range)

• \$D\$5 ▶ this is a sample cell filled with yellow.
• D5:D13 ▶ cell range to perform the sum operation.

Note:

• Formula to sum up the Blue cells:
=SumColoredCells(\$D\$8,D5:D13)

Cell \$D\$8 is a sample Blue cell.

• Formula to sum up the Orange cells:
=SumColoredCells(\$D\$11,D5:D13)

Cell \$D\$11 is a sample Orange cell.

## Related Articles

Get FREE Advanced Excel Exercises with Solutions!
Mrinmoy Roy

Mrinmoy Roy, a dedicated professional with a BSc in Electronics and Communication Engineering from Khulna University of Engineering & Technology, Bangladesh, brings over two years of expertise to the ExcelDemy project. As a prolific contributor, he has authored around 180 articles, showcasing his deep knowledge and passion for Microsoft Excel, Data Analysis, and VBA. His unwavering commitment to continuous learning, combined with versatile skills, renders him well-suited for roles in data management and spreadsheet solutions. He has interest... Read Full Bio

1. Hi,

First of all thanks for the guide. The fourth part does the trick for me for 95%. What seems to be missing is that when I change the color of a cell, the SumColoredCells method doesn’t automatically recalculate, do you have a fix for this?

Kr,

• Hello Nicholas,
There’s no easy way to make a User-Defined Function dynamic. However, you can use an event procedure using the Worksheet_SelectionChange event to recalculate each time you change cell color. This will recalculate the formula whenever you prompt an event in your worksheet.
But I don’t recommend you to use this technique. Because it’ll slow down your workflow in Excel. Using the event procedure, the UDF will continue to calculate each time you click on your sheet.

However, you can press CTRL + ALT + F9 to recalculate manually each time you change cell color. It’s the best solution to your problem so far.
Regards!

2. I tried this fourth method with success, but it is rounding decimals to the next whole number. How do I get the formula to keep to the second decimal place.

• Hi Andrew,
It happens because of the variable types. The two variables X & Y currently have the variable type “Long” and “Integer” respectively. To get a sum value up to 2 decimal places, make both variable types “Double”. This will reserve the decimal places.

Here’s the modified code:

Function SumColoredCells(CC As Range, RR As Range)
Dim X As Double
Dim Y As Double
Y = CC.Interior.ColorIndex
For Each i In RR
If i.Interior.ColorIndex = Y Then
X = WorksheetFunction.Sum(i, X)
End If
Next i
SumColoredCells = X
End Function

I hope this will work. Regards!

3. Hello! I used method 4 and it worked great, however, when I added a new sheet to the workbook it’s now giving me the #name? error in all the formulas. I did not change the vba nor formulas; just insert a tab and got the errors

• Hello Jaye,
Hope you are doing well.
#NAME error appears when you have a syntax problem in your formula. There shouldn’t be any #NAME error appearance when you add a new sheet to your workbook. We have checked out the VBA and the function and didn’t find any problem.

We recommend you share your file or at least a Screenshot of the problem with us and we will get back to you about a solution.

Regards
Hassan Shuvo || ExcelDemy

4. Nice job. This was great. Option 3 & 4 were what I needed, however 3 didn’t work (just displaced the formula in the cell instead of result), but option 4 worked great.
Best regards,
Steve

• Hi STEVE,
Thanks for your appreciation. And also sorry to hear that you faced difficulties with the 3rd method. I think you have faced problem in the GET.CELL function. Previously the sheet name (GET.CELL!) was also included in the formula and this may have occurred error. Actually there is no need to use the sheet name in the formula because Excel automatically updates the sheet name in the Name Manager. So, we have updated the formula to “=GET.CELL(38,\$D5)“. Try it and hope you will be able to make it now. Thanks again for your observation.

Regards
Rafiul Hasan
Team ExcelDemy

Advanced Excel Exercises with Solutions PDF