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.

**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:

`=SUBTOTAL(109,D5:D7)`

- 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.

`=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.**

**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:

`=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.

**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)**

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!

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!

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.

#NAMEerror appears when you have a syntax problem in your formula. There shouldn’t be any#NAMEerror 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

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 ““. Try it and hope you will be able to make it now. Thanks again for your observation.`=GET.CELL(38,$D5)`

Regards

Rafiul HasanTeam

ExcelDemy