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

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

How to Sum Columns in Excel by Color: 6 Easy Methos

Let’s assume we have the below dataset that contains several items sold in 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.

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

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

This time I will find out the index 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.

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

• 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 

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.

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

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.

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

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 for your range and type the below formula in the Refers to box. Then press OK and close the Name Manager dialog.

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

• 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 

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 in Blue color.

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 Practice Workbook

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

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.

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