Excel is a great tool for visualizing and analyzing data. While working with Excel sometimes it becomes necessary to sum the values of the colored cells or sum the number of colored cells quickly. In this article, we’ll demonstrate ** 5** convenient and quick methods to sum colored cells in Excel without VBA. So, let’s dive into the article to get to know the ways to do this job.

## How to Sum Colored Cells Without VBA in Excel: 5 Ways

Here, I have a sales report for ** “X” Company**. It concludes the

**,**

*Product***, and**

*Region***in columns**

*Sales***B**,

**C**, and

**D**respectively. In this dataset, for

**, the**

*Apples***are colored in Green.**

*Sales** 📓 Note: This is a basic dataset to keep things simple. In a practical scenario, you may encounter a much larger and more complex dataset*.

Using the following methods, you will be able to sum the

**value based on this color or sum the number of Green colored cells in this data range.**

*Sales*Not to mention, for this purpose, we are using

*Microsoft Excel 365*version, you may use any other version according to your convenience. Please leave a comment if any part of this article does not work in your version

### 1. Using SUMIF Function

To get the ** Total Sales of Apple** based on its color you can use

**the SUMIF function**. To do this task, I have added a column named

**.**

*Color***📌**** Steps:**

➤ First of all, write the color of cells of the ** Sales** column manually in the

**column.**

*Color*➤ Then, select the output cell **E12**.

➤ After that, type the following formula.

`=SUMIF(E5:E11,"Green",D5:D11)`

**E5:E11** is the ** criteria_range**,

**Green**is the

**and**

*criteria***D5:D11**is the

**.**

*sum_range*➤ Later, press

**ENTER**.

**Result:**Now, you will get the

**which is**

*Total Sales of Apple***.**

*$8,863*### 2. Creating Table to Sum Values of Colored Cells

If you want to know the ** Total Sales of Apple** based on its color, you can create a

**Table**and use

**the SUBTOTAL function**.

**📌**** Steps:**

➤ Firstly, select the data table (**B4:D11**).

➤ Next, go to **Insert** Tab>>**Table** Option

Then the **Create Table** dialog box will appear.

➤ Click the **My table has headers** option.

➤ As usual, press **OK**.

After that, the table will be created.

➤ Then, click the **Dropdown **icon in the ** Sales** column.

➤ Select the

**Filter by Color**option.

➤ Hence, choose the green box as

**Filter by Cell Color**.

➤ As always, press

**OK**.

Now, the table will be filtered by green.

➤ Select the output cell **D12**.

➤ Type the following formula.

`=SUBTOTAL(109,D5:D9)`

**109** is for the **SUM function**, and **D5:D9** is the range of cells containing ** Sales**.

➤ Press

**ENTER**.

**Result:**Afterward, you will get the

**which is**

*Total Sales of Apple***.**

*$8,863***Read More:** **How to Sum Filtered Cells in Excel (5 Suitable Ways)**

### 3. Utilizing Filter Option to Sum Colored Cells

A **Filter** is a useful tool that helps us display only the specified values in Excel. Based on the filtered result, we can later edit, copy, chart, or print only the visible values. We can use this option to sum the values of colored cells in Excel too.

#### 3.1 Sum Values of Colored Cells

You can have the ** Total Sales of Apple** based on its color by using the

**Filter**option and the

**SUBTOTAL function**.

**📌**** Steps:**

➤ Firstly, select the output cell **D12**.

➤ Later, type the following formula

`=SUBTOTAL(109,D5:D11)`

**109** is for the **SUM function**, and **D5:D11** is the range of cells.

➤ Press **ENTER**.

Then, you will get the total ** Sales**.

➤ Secondly, select the data range.

➤ Therefore, go to **Data** tab>>**Sort & Filter** dropdown>> **Filter** option.

You can see filter buttons beside each column heading.

➤ Thirdly, click the **Dropdown **icon in the ** Sales** column.

➤ Right now, select the

**Filter by Color**option.

➤ Choose the green box as

**Filter by Cell Color**.

➤ Then, press

**OK**.

**Result:**Afterward, you will get the

**which is**

*Total Sales of Apple***.**

*$8,863*#### 3.2 Count of Colored Cells

If you want to know the sum of the number of Green colored cells or count the Green colored cells, you can use the **Filter** option and the **SUBTOTAL function**.

**📌**** Steps:**

➤ Initially, select the output cell **D12**.

➤ Type the following formula.

`=SUBTOTAL(103,D5:D11)`

**103** is for **the COUNTA function**, and **D5:D11** is the range of cells.

➤ Press **ENTER**.

Now, you will get the sum of the total number of cells.

➤ Follow the **previous steps** of **Method-3.1**.

Finally, you will get the sum of the number of Green colored cells.

### 4. Incorporating Find & Select Option

If you have the sum of the number of Green colored cells or have counted the Green colored cells, you can use the **Find & Select** option.

**📌**** Steps:**

➤ Primarily, select the data table (**B4:D11**).

➤ After that, go to **Home** tab>>**Editing** dropdown>>**Find & Select** dropdown>>**Find** option.

After that, the **Find and Replace** dialog box will pop up.

➤ Secondarily, select the **Format** option.

Then, the **Find Format** dialog box will appear

➤ Hence, select the **Fill** tab and choose the **Green** color.

➤ Press **OK**.

➤ Consequently, click **Find All**.

**Result:**Then, you can see the total number of Green colored cells in the bottom corner of the dialog box, which indicates that there is a total of

*green colored cells.*

**3**### 5. Applying GET.CELL Method

In this method, we are going to use an unconventional approach. Are you curious about it? Then see the following.

#### 5.1 Sum Values of Colored Cells

You can use the **GET.CELL function** to sum up the ** Sales** for Green colored cells.

**📌**** Steps:**

➤ Go to **Formulas** tab>>**Defined Names** dropdown>>**Name Manager** option.

Then, the **Name Manager** Wizard will appear.

➤ Select the **New** option.

After that, the **New Name** dialog box will pop up.

➤ Type any type of name in the **Name** box, here I have used **ClrCode**.

➤ Select the **Workbook** option in the **Scope** box.

➤ Type the following formula in the **Refers to** box.

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

**38** will return the **Color Code** and **SUM!$D5** is the colored cell in the ** SUMIF** sheet.

➤ Finally, press

**OK**.

➤ Create a column named ** Code**.

➤ Type the following formula in the output cell **E5**.

`=ClrCode`

➤ Press **ENTER**.

It is the function we created in the previous step, and it will return the ** Code** of the colors.

➤ Double-click on the Fill Handle Tool.

In this way, you will get the color codes for all of the cells.

➤ Select the output cell **G5**.

➤ Type the following formula.

`=SUMIF(E5:E11,ClrCode,D5:D11)`

**E5:E11** is the ** criteria_range**,

**ClrCode**is the

**and**

*criteria***D5:D11**is the

**.**

*sum_range***Result:**Now, you will get the

**which is**

*Total Sales of Apple***.**

*$8,863***📓 **** Note: **You have to save the Excel file as a Macro-enabled Workbook because of the

**GET.CELL function**.

#### 5.2 Count of Colored Cells

You can use the **GET.CELL function** to sum the number of Green colored cells.

**📌**** Steps:**

➤ Follow the **previous steps** of **Method-5.1**.

➤ Select the output cell **G5**.

➤ Type the following formula.

`=COUNTIF(E5:E11,ClrCode)`

**E5:E11** is the ** criteria_range**, and

**ClrCode**is the

**.**

*criteria***Result:**After that, you will get the total number of Green colored cells in the range.

## How to Sum Colored Cells in Excel with VBA

Have you ever thought of automating the same boring and repetitive steps in Excel? Think no more, because VBA has you covered. In fact, you can automate the prior method entirely with the help of VBA. So, without further delay, let’s dive in!

**📌**** Steps:**

➤ To begin with, go to the **Developer** tab >> **Visual Basic** option.

Suddenly, the **Microsoft Visual Basic for Applications** window will open.

➤ Then, jump to the **Insert** tab.

➤ After that, select **Module** from the options.

It opens the code module where you need to paste the code below.

```
Function Sum_Red_Cells(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
Sum_Red_Cells = x
End Function
```

After that, navigate to the VBA worksheet “**VBA**”.

➤ Then, select cell **D14 **and start to write the function name we just created.

Surprisingly, you can see that the function name appears just after writing down **=sum** in the cell.

➤ Later, select the function **Sum_Colored_Cells** and press the **TAB** key on the keyboard.

At this point, give the necessary arguments for the function. **D13** is the cell reference for the green-colored cell. **D5:D11** is the cell range to perform the sum operation.

## Practice Workbook

For doing practice by yourself we have provided a Practice section like below in each sheet on the right side. Please do it by yourself.

**Download Practice Workbook**

You may download the following Excel workbook for better understanding and practice.

## Conclusion

This article explains how to sum colored cells without VBA in Excel in a simple and concise manner. Thank you for reading this article. We hope this was helpful. Please let us know in the comment section if you have any queries or suggestions.

## Further Readings

- How to Sum Random Cells in Excel
- How to Sum in Excel If the Cell Color Is Red
- [Solved!] Currency Sum Not Working in Excel
- How to Sum Visible Cells with Criteria in Excel
- How to Ignore Blank Cells in Excel Sum

**<< Go Back to Sum in Excel | Calculate in Excel | Learn Excel**