In this article, I will discuss how you can get the sum of cells in excel columns by color. Often, while working with** Microsoft Excel**, we use different fill colors to cells for a better understanding of the containing data. Later, at times we need to calculate the sum of cell values based on their background color. Unfortunately, there is no direct way to calculate the summation of cell values in columns. So, I will explain several methods to get the calculation done.

**Table of Contents**hide

## Download Practice Workbook

You can download the practice workbook that we have used to prepare this article.

## 6 Methods to Sum Columns by Color in Excel

Let’s assume we have the below dataset that contains several items sold quantity. I have applied different fill colors to the **Sold Quantity** column.

Now, I will calculate the summation of the cell values of the above columns using the below methods.

### 1. Excel SUMIF Function to Get Sum of Columns by Color

In this method, I will add a helper column to the parent dataset and then apply **the SUMIF function** to get the final sum.

**Steps:**

- First, add a helper column to the main dataset and type the color of the cells manually.

- Next, type the below formula in
**Cell G5**and press**Enter**.

`=SUMIF(D5:D16,"Blue",C5:C16)`

- Upon entering the formula, we will get the sum of the cells that are in
**Blue**color. From the result, we can see that the total of**Blue-colored**cells is**800**.

- Similarly, to get the total amount of
**Yellow-colored**cells, use the below formula.

`=SUMIF(D5:D16,"Yellow",C5:C16)`

**Read More:** **How to Add Up Columns in Excel (12 Methods)**

### 2. VBA UDF to Add up Cells of Columns Based on Color

This time I will find out the indexes colors used in columns using a **VBA User Defined Function**. Then based on those color indexes, I will apply the **SUMIF **function to get the ultimate sum.

**Steps:**

- First, go to
**Developer**>**Visual Basic**to bring the**VBA**window. Or you can use**Alt + F11**to get the window.

- Next, from the
**VBA**window, right-click on the**VBAProject**, and**Insert**a new**Module**.

- Now type the below code in the
**Module**to create the**UDF**.

```
Function FindIndex(n As Range) As Integer
FindIndex = n.Interior.ColorIndex
End Function
```

- Then go to the excel sheet where you want to apply the newly created
**UDF**. - After that, insert a new column (
**Color Index**) in the parent dataset and type the below formula in**Cell D5**.

`=FindIndex(C5)`

- Once you hit
**Enter**, the above formula will return the following result. Use the**Fill Handle**(**+**) tool to copy the formula to get the color indexes of the rest of the cells.

- Hence, here we get the color indexes.

- Now, we will calculate the sum of cell values of columns based on the above color indexes. To do that, type the following formula in
**Cell G5**. - Press
**Enter**after that.

`=SUMIF($D$5:$D$16,D5,$C$5:$C$16)`

- As a result, excel will return the summation of
**Blue-colored**cells which is**800**. - Later, you can drag the
**Fill Handle**tool to get the total of**Yellow**and**Green**colored cells.

**Read More:** **How to Sum Entire Column in Excel (9 Easy Ways)**

### 3. Calculate Total of Colored Cells in Columns Using VBA UDF Directly

In the previous method, I used **VBA UDF **along with the** SUMIF** function to calculate the sum of cell values in columns based on color. But, now I will use only **VBA UDF** to get the expected summation.

**Steps:**

- Firstly bring the
**VBA**window by following the path:**Developers**>**Visual Basic**. - Secondly,
**Insert**a**Module**by right-clicking the**VBAProject**. - Then, type the below code in the newly inserted
**Module**.

```
Function SumColor(rng As Range, TotalColor As Range)
Dim TotalColorValue As Integer
Dim OverallSum As Long
TotalColorValue = TotalColor.Interior.ColorIndex
Set Cell = rng
For Each Cell In rng
If Cell.Interior.ColorIndex = TotalColorValue Then
OverallSum = OverallSum + Cell.Value
End If
Next Cell
SumColor = OverallSum
End Function
```

- Then go to the worksheet where you want to get the sum of colored cells. Type the below formula in
**Cell F5**and hit**Enter**.

`=SumColor($C$5:$C$16,C5)`

- Upon entering the above formula, Excel will return the below result. As expected, here we received the summation based on cell color.

**Read More:** **Sum Every nth Column in Excel(Formula and VBA Code)**

### 4. Apply SUBTOTAL Function & Excel Filter to Get Sum of Columns According to Color

This time I will calculate the total cell values using **the SUBTOTAL function** in excel. Then I will apply the **Filter** option to get the sum of values columns based on cell color.

**Steps:**

- First, type the below formula in
**Cell C18**. Next press**Enter**.

`=SUBTOTAL(9,C5:C16)`

- As a consequence, we will get the total of all sold quantities.

- Now, I will apply
**Filter**to the dataset, to do that, select any of the cells in the dataset, and go to**Data**>**Filter.**Or you can use the keyboard shortcut**Ctrl + Alt + L**too.

- Once the
**Filter**is applied, click on the drop-down menu of**Sold Quantity**column. - Then click on
**Filter by Color**and choose any color from the**Filter by Cell Color**. I have selected the color**Blue**.

- As a result, all the cells that are blue are filtered and we have the summation of those cells.

- Similarly, if we filter by
**Green**color, the following will be the sum.

**Read More:** **How to Sum Columns in Excel When Filtered (7 Ways)**

### 5. Add up Cells of Columns by Color with Excel GET.CELL Function

In this method, we will use the** GET.CELL **function to find out the color indexes of the cell colors used in columns. Then as usual we will apply the **SUMIF **function to calculate the summation based on a certain color. **GET.CELL** function is a hidden function in excel. So, to apply this function you have to follow the below steps.

**Steps:**

- Firstly, go to
**Formulas**>**Define Names**.

- Consequently, the
**New Name**dialog will appear. Enter a suitable name to your range and type the below formula in the**Refers to**box. Then press**OK**and close the**Name Manager**dialog.

`=GET.CELL(38,$C5)`

- Add an extra column to the parent dataset to list the color indexes. Type the name of the range in
**Cell D5**.

`=Color_Index`

- Similar to other excel formulas, use the
**Fill Handle**tool to get the color indexes for the rest of the colored cells.

- Now, type the
**SUMIF**formula in**Cell F5**and press**Enter**to get the summation of values according to cell color.

**Read More:**** How to Total a Column in Excel (7 Effective Methods)**

### 6. Sum by Color Using Excel Table Design

We can use the** Table Design** tab to get the sum of cell values in columns by color.

**Steps:**

- Initially, I will convert my dataset into a table by pressing
**Ctrl +T**.

- Then select the table, and the
**Table Design**tab will appear. - Now, click on the
**Table Design**tab, and put a checkmark on the**Total Row**option.

- As a consequence, we will get the total of all sold quantities as below:

- Filtering the table data based on color will give us the expected result. For instance, I have filtered by the color:
**Blue**.

- Finally, here is the summation of cell values of the
**Sold Quantity**column by**Blue**color.

**Read More:** **How to Sum Columns in Excel Table (7 Methods)**

## Things to Remember

- You can get the sum of cell values from columns by font color too.
- You can use
**Excel VBA**to calculate the sum of colored cells from an entire workbook.

## Conclusion

In the above article, I have tried to discuss several methods to sum cells of columns by color in excel elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.