The below dataset contains several items sold in quantity. Different fill colors have been applied to the **Sold Quantity** column.

### Method 1 – Using the SUMIF Function

**Steps:**

- Add a helper column to the main dataset and type the color of the cells manually.

- Enter the below formula in
**cell G5**and press**Enter:**

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

- We will get the sum of the
**blue**cells. The total number of blue cells is**800**.

- To get the total amount of
**yellow-colored**cells, enter the following formula:

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

### Method 2 – Using VBA UDF

**Steps:**

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

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

- Enter the below code in the Module to create the
**UDF:**

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

- Go to the Excel sheet where you want to apply the newly created
**UDF**. - Insert a new column (
**Color Index**) in the parent dataset and enter the below formula in cell**D5:**

`=FindIndex(C5)`

- Hit Enter, and 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.

- We get the color indexes.

- Calculate the sum of column cell values based on the above color indexes. To do that, enter the following formula in cell
**G5:** - Press
**Enter**.

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

- Excel will return the summation of
**Blue-colored**cells, which is**800**. - Drag the
**Fill Handle**tool to get the total of**Yellow**and**Green**colored cells.

**Read More:** **How to Sum Entire Columns in Excel **

### Method 3 – Using VBA UDF Directly

**Steps:**

- Go to the
**VBA**window:**Developers**>**Visual Basic**. **Insert**a**Module**by right-clicking the**VBAProject**.- Enter 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
```

- Go to the worksheet where you want to get the
**sum of colored cells**. Enter the below formula in cell**F5**and hit**Enter**.

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

- Excel will return the below result. Here we received the summation based on cell color.

### Method 4 – Using the SUBTOTAL Function & Excel Filter

**Steps:**

- Enter the below formula in cell
**C18:** - Press
**Enter**.

`=SUBTOTAL(9,C5:C16)`

- We will get the total of all sold quantities.

- Apply
**Filter**to the dataset. Select any of the cells in the dataset, and go to**Data**>**Filter.**

Or

- Use the keyboard shortcut
**Ctrl + Alt + L.**

- Click on the drop-down menu of the
**Sold Quantity**column. - Click on
**Filter by Color**and choose any color from the**Filter by Cell Color**. I have selected the color**Blue**.

- All the cells that are blue are filtered and we have the summation of those cells.

- If we filter by
**Green**, the following will be the sum.

**Read More:** **How to Sum Columns in Excel When Filtered **

### Method 5 – Using the Excel GET.CELL Function

**Steps:**

- Go to
**Formulas**>**Define Names**.

- The
**New Name**dialog will appear. Enter a suitable name for your range and enter the below formula in the**Refers to**box. - 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. Enter the name of the range in cell
**D5**.

`=Color_Index`

- Use the
**Fill Handle**tool to get the color indexes for the rest of the colored cells.

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

### Method 6 – Using Excel Table Design

**Steps:**

- Convert the dataset into a table by pressing
**Ctrl +T**.

- Select the table, and the
**Table Design**tab will appear. - Click on the
**Table Design**tab and put a checkmark on the**Total Row**option.

- We will get the total of all sold quantities as below:

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

- Here is the summation of the
**Sold Quantit**y column cell values in**blue**.

**Read More:** **How to Sum Columns in Excel Table **

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

**Download the Practice Workbook**

Download the workbook to practice.

## Related Articles

**How to Calculate Total Row and Column in Excel****How to Sum Every Nth Column in Excel****How to Sum Multiple Rows and Columns in Excel**

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