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:

• 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:

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:

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

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

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

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

• Add an extra column to the parent dataset to list the color indexes. Enter the name of the range in cell D5.

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

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