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:

• Press the Enter button.

Read More: How to Sum Selected Cells in Excel (4 Easy Methods)

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:

• Press Enter.

Read More: How to Sum Filtered Cells in Excel (5 Suitable Ways)

Similar Readings

• How to Sum by Group in Excel (4 Methods)
• [Fixed!] Excel SUM Formula Is Not Working and Returns 0 (3 Solutions)
• How to Sum Only Positive Numbers in Excel (4 Simple Ways)
• Sum by Font Color in Excel (2 Effective Ways)
• How to Sum Range of Cells in Row Using Excel VBA (6 Easy Methods)

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.

• Hit the OK button.

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

• Press the Enter button.

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

• Select cell C16 and enter the formula:

• Hit Enter.

Read More: Sum to End of a Column in Excel (8 Handy Methods)

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:

• Hit Enter.

Note:

• Formula to sum up the Blue cells:

Cell $D$8 is a sample Blue cell.

• Formula to sum up the Orange cells:

Cell $D$11 is a sample Orange cell.

Read More: Sum Cells in Excel: Continuous, Random, With Criteria, etc.

Download the Practice Workbook

Related Articles

• How to Sum Only Visible Cells in Excel (4 Quick Ways)
• Sum All Matches with VLOOKUP in Excel (3 Easy Ways)
• Sum If a Cell Contains Text in Excel (6 Suitable Formulas)
• How to Sum Multiple Rows in Excel (4 Quick Ways)