# How to Sum Columns by Color in Excel (6 Easy Methods)

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.

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

## Related Articles

<< Go Back to Sum ColumnsSum in Excel

Get FREE Advanced Excel Exercises with Solutions!
Hosne Ara

Hosne Ara is a materials and metallurgical engineer who loves exploring Excel and VBA programming. To her, programming is like a time-saving superhero for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B. Sc. in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, she's shifted gears and now works as a content developer. In this role, she... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF