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.
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.
- 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.
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.
- 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.
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.
- 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.
- 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 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.
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.
- Firstly, go to Formulas > Define Names.
- 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.
- 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.
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.
- 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 by Blue color.
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.
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.