Updated August 21, 2023
Sum by Color in Excel
In this article, we will learn about Sum By Color in Excel. In Excel, we have a function for adding numbers. But there is no direct way to add the number by their background color. By this, we don’t need to sum the numbers separating the colored cells. We can directly consider all the cells in the formula and sum them as per their background color.
This we can do when we have many cell numbers colored, and filtering the data is not suggested there.
How to Sum by Color in Excel?
Excel Sum by Color is very simple and easy. Let’s understand how to sum by color in Excel with some examples.
Sum by Color in Excel – Example #1
Here we have data on some product and their sale. As shown below, column C has numbers with some background color.
Now go to the cell where we need to see the output and type the “=” sign (Equal). And search and select the SUBTOTAL function as shown below.
Now, as we need to sum the numbers, so from the drop-down of SUBTOTAL Function, select 9, which is for sum.
And for reference1, select the complete range of column C, which we need to total as shown below.
The Output will be as given below.
Now apply the filter in the top row by pressing Ctrl + Shift +L.
Go to Filter by Color from the drop-down menu of it. Select any color; we have selected YELLOW, as shown below.
Once we do that, we will get the Output cell filtered sum as 190, as shown below.
We can also check the correctness of the applied SUBTOTAL formula by filtering the different colors.
Sum by Color in Excel – Example #2
There is another way to sum the numbers by their colors. For this, we will consider the same data as shown in example-1. Now copy the column’s cells with numbers and paste them into a separate sheet or in the same sheet in a different location.
Now quickly press Ctrl + T. This will enable the selected cells to convert into table format. Now click on Ok from the Create Table box.
Once we do that, selected cells will convert into the table form. And another menu will add with the name Design in the menu bar. Now Check and tick the Total Row option from the Table Style Options.
Once we do that, we will get the sum of cells at the bottom end of the column with a drop-down menu. Here we are getting a sum of 786.
Now from the drop menu of the total sum, select the Sum option as shown below.
By this, we enable the table to sum the filtered data as per colored cells. Now go to the top filter drop-down of the same column and select any color to get summed up from the Filter by Color option. Select any color; we have selected YELLOW, as shown below.
Once we do that, we will get the YELLOW colored filtered and the sum of the YELLOW colored cells in the below cell.
Sum by Color in Excel – Example #3
There is another method of summing the numbers by their color. VBA Marcos will do this. For this, we will consider the same data we saw in example-1. And we will add separate cells for each product name to get the sum of their quantity sold.
Now press Alt + F11 to enter Visual Basic for the Application screen.
Now go to the Insert menu and select Module.
This will open a new Module to write code. Now in the blank Module, write the code for enabling the sum by color function in Excel, as shown below. You can also use the same code to make some changes in that.
Close the complete window of VBA. Now go to the cell reference of Mobile, where we need to see the result and type the “=” sign. Now search and select the Sum Color function we created in VBA.
And select the reference colored cell and then select the range to get summed, as shown below.
The Result will be as shown below.
Once done, drag the formula to complete respective cells to see the result as shown below.
As we can see in the above screenshot, the sum of yellow-color cells is coming at 190, which the summed value is obtained in example-1 and example-2. This means that all the formulas and functions used in all examples are correct.
Pros
- Sum by color from the SUBTOTAL function is the easiest way to get the sum result by color in Excel.
- The process steps shown in example-2 take a little longer than in example-1, but it is still easy to apply.
- We don’t need to filter the colored cells separately to get the sum.
Cons
- Sum by color shown in example-3 by VBA coding takes time, and it doesn’t show the result if we paste the data in another file because it does carry the code with it.
Things to Remember About Sum by Color in Excel
- If you are summing colored cells by VBA Coding, it is always recommended to save in the Macro enabled Excel; this will save the coding for future use.
- These methods can use anywhere, irrespective of the data size. It is always recommended to use this method when we have a huge set of data, where if we filter the data to get the summed value may crash the file.
Recommended Articles
This has been a guide to Sum by Color in Excel. Here we discuss how to sum by color in Excel, practical examples, and a downloadable Excel template. You may also look at the following articles to learn more –
