There’re no built-in Excel functions that **sum up the colored cells** in Excel by themselves. Yet multiple ways can manage to sum up the cells based on their cell colors. In this blog post, you will learn 4 distinct ways, to sum up, the colored cells in Excel with easy examples and proper illustrations.

In the below image you will find an overview of the whole article.

We will be using a **Product Price List** data table to demonstrate all the methods, to sum up, colored cells in Excel.

So, without having any further discussion let’s get into all the methods one by one.

**Table of Contents**hide

## 1. Using Excel SUMIF Function to Sum Colored Cells

Suppose, you want to sum up the total price of the products having “**MTT**” in their product ids. To mark those products, you have attributed them with blue color. Now, we will discuss a formula that will sum up the values of the cells indicated by blue color. To do so, we can use **the SUMIF function**. Now follow the steps below to see how to do it.

**🔗 Steps:**

❶ First of all, add an **extra column** to specify the cell colors in column “**Price**”.

❷ Then select cell **C16** ▶ to store the formula result.

❸ After that **type**

`=SUMIF(E5:E13,"Blue",D5:D13)`

❹ Finally press the **ENTER** button.

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

## 2. Using AutoFilter and SUBTOTAL to Add Colored Cells

We can use the **AutoFilter** feature and **the SUBTOTAL function** too, to sum the colored cells in Excel. Here are the steps to follow:

**🔗 Steps:**

❶ First of all, **select** the whole data table.

❷ Then go to the **Data** ribbon.

❸ After that, click on the **Filter** command.

❹ Now click on the **dropdown icon** at the corner of the **Price** column header.

❺ Then from the dropdown menu select **Filter by Color.**

❻ Then click on the **blue color rectangle.**

❼ Now **select** cell **C16** ▶ to store the formula result.

❽ **Type**

`=SUBTOTAL(109,D5:D7)`

❾ Finally finish the whole process by pressing the **ENTER** button.

That’s it.

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

## 3. Using Excel GET.CELL Function to Sum Colored Cells

You can use **the GET.CELL function** along with **the SUMIF function** to sum up the colored cells in Excel. Now follow the steps below to see how to incorporate them together, to sum up, the colored cells.

**🔗 Steps:**

❶ First of all, go to **Formulas** ▶ **Defined Names** ▶ **Name Manager.**

The **Name Manager** dialog box will pop up. From that box:

❷ Click on **New**.

After that, the **Edit Name** dialog box will pop up on the screen. From there,

❸ Assign a name, for example, **Code** within the **Name** bar.

❹ **Type** the following code within the **Refers to** the bar.

`=GET.CELL(38,GET.CELL!$D5)`

❺ After that hit the **OK** button.

❻ Now you have to **create** a new column. For instance, the **Code** is as follows.

❼ Select cell **E5** and **type**

`=Code`

- Then, press the
**ENTER**button.

❽ Now drag the **Fill Handle** icon to the end of the **Code** column.

❾ Now select cell **C16** and enter the formula:

`=SUMIF(E5:E13,33,D5:D13)`

❿ Finally, terminate the process by pressing the **ENTER** button.

So, here comes the result!

**␥ Formula Breakdown**

**=GET.CELL(38,GET.CELL!$D5) ▶****38**refers 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 as we have 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)**

## 4. Using Excel VBA to Add Colored Cells

You can also sum up the colored cells by using the **VBA code**. In this section, we will be creating a user-defined function using VBA, to sum up, the colored cells.

Now follow the steps below:

❶ First of all, press the **ALT+F11** button to open the **Excel VBA** window.

❷ Now, go to the **Insert ▶ Module.**

❸ After the 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
```

❹ Now **paste and save** this code in the **VBA** editor.

❺ Now select cell **D16** ▶ to store the sum result.

❻ Enter the **code** within the cell:

`=SumColoredCells($D$5,D5:D13)`

This code will sum up all the cells indicated by yellow color.

❼ Finally, hit the **ENTER** button.

- Then, drag the “
**Fill****Handle**” down to fill.

**␥ Formula Breakdown**

📌 **Syntax** *=SumColoredCells(colored_cell,range)*

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

**📓 Note:**

- Formula to sum up the
**Blue**painted cells:

`=SumColoredCells($D$8,D6:D14)`

Where cell **$D$8** is a sample **Blue** painted cell.

- Formula to sum up the
**Orange**painted cells:

`=SumColoredCells($D$11,D7:D15)`

Where cell **$D$11** is a sample **Orange** painted cell.

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

## Things to Remember When Adding Colored Cells in Excel

📌 Be careful about the syntax of the functions.

📌 Insert the data ranges carefully into the formulas.

**Download Practice Workbook**

You are recommended to download the Excel file and practice along with it.

## Conclusion

To wrap up, we have illustrated 4 different methods overall, to sum colored cells in Excel. Moreover, you can download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. Surely we will try to respond to all the relevant queries asap.

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