Excel Sum by Color (Table of Contents)
Sum by Color in Excel
In excel we have a function of adding the 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 really 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 we can see below, column C has numbers with some background color.
Now go to the cell where we need to see the output and type “=” sign (Equal). And search and select 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.
4.9 (2,423 ratings)
View Course
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, here 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 check the correctness of the applied SUBTOTAL formula by filtering the different colors as well.
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 it in separate sheet or in the same sheet in a different location.
Now quickly press Ctrl + T. This will enable the selected cells to get converted into table format. Now click on Ok from the Create Table box.
Once we do that, selected cells will get converted into the table form. And another menu will get added with the name Design in the menu bar. Now Check 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 sum as 786.
Now from the drop menu of the total sum, select 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 required colored to get summed up from Filter by Color option. Select any color, here 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 as shown below.
Sum by Color in Excel – Example #3
There is another method of summing the numbers by their color. This will be done by VBA Marcos. For this, we will consider the same data which we have seen in example-1. And we will add separate cells of each product name to get the sum of their quantity sold.
Now press Alt + F11 to get into Visual Basic for Application screen.
Now go to Insert menu and select Module.
This will open a new Module to write code. Now in blank Module, write the code for enabling sum by color function in excel as shown below. You can use the same code to make some changes in that as well.
Close the complete window of VBA. Now go the cell reference of Mobile where we need to see the result and type “=” sign. Now search and select Sum Color function which we have 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 colored cells is coming as 190. Which the summed value as obtained in example-1 and example-2. Which actually mean that all the formula 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.
- Process steps shown in example-2 takes little more time than of example-1 but still, it is easy to apply.
- We don’t need to filter the colored cells separately to get the sum.
Cons
- Sum by color showed 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 cell by VBA Coding then it is always recommended to save in the as Macro enabled excel, by doing this it will save the coding as well for future use.
- These methods can be used anywhere irrespective to the size of data. In fact, 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 along with practical examples and downloadable excel template. You may also look at the following articles to learn more –
