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


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.

6 Methods to Sum Cells of Columns by Color in Excel

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.

Excel SUMIF Function to Get Sum of Columns by Color

  • Next, type the below formula in Cell G5 and press Enter.
=SUMIF(D5:D16,"Blue",C5:C16)

Excel SUMIF Function to Get Sum of Columns by Color

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

VBA UDF to Add up Cells of Columns Based on Color

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

VBA UDF to Add up Cells of Columns Based on Color

  • 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

VBA UDF to Add up Cells of Columns Based on Color

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

VBA UDF to Add up Cells of Columns Based on Color

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

VBA UDF to Add up Cells of Columns Based on Color

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

VBA UDF to Add up Cells of Columns Based on Color

  • 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

Calculate Total of Colored Cells in Columns Using VBA UDF Directly

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

Calculate Total of Colored Cells in Columns Using VBA UDF Directly

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

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

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

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

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

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

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

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

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

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

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

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

  • 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 up Cells of Columns by Color with Excel GET.CELL Function

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

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

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

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

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

Sum by Color Using Excel Table Design

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

Sum by Color Using Excel Table Design

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

Sum by Color Using Excel Table Design

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

Sum by Color Using Excel Table Design

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


Related Articles

Hosne Ara

Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo