Let me help you illustrate with an example, the problem statement. To make the task more clearly, let us have a look at the image below.
As this image shows that here we don't need the sum total of all elements, but instead want the sum of the elements that have the same background color.
Recommended reading: Weighted sum in Excel
We know that the SUMIF function is a combination of the sum and if formula, and hence SUMIF can come quite handy for adding color based cells.
If you don't know how to use the SUMIF function, so before I go any further I strongly suggest you to read this post.
First of all let's try to understand how we do this:
Consider that we have a table as shown in the image below.
Then, we will add a column to this table where we will manually enter the background colors of its adjacent cells as shown in the image below.
Similarly, to find the sum of Orange and green, background cells will use the formulas = SUMIF (B2: B13, "Orange", A2: A13) e = SUMIF (B2: B13, "Green", A2: A13) respectively.
But, as we can see that this method is rather complicated, especially if we have to use this on lists with hundreds of elements.
So what's the alternative faster?
To make the above process, easier to use we need to reduce the effort of writing the cell background colors manually.
So, for this task we can use a small user-defined function (UDF), which will do the trick for us.
ColorIndex function (CellColor as range) ColorIndex = CellColor.Interior.ColorIndexEnd function
Note: This function does not return the color name, but returns the color index which is also a unique value and can be used in our task.
Follow the steps below to use the UDF:
1. first Open your spreadsheet where you need to add the cells based on background colors.
2. then, press ALT + F11 to open the VB Editor. navigate to ' Insert ' > ' module '.
3. after that, paste the "ColorIndex" UDF in the Editor.
4. now, add a column next to the range that you want to summarize. In this new column, enter the formula as:
ColorIndex = (
5. after that, drag this formula to the entire range.
6. now, you can use the SUMIF function to add cells that have the same background color, as shown in the image above.
The UDF that will use this method is simply an extension of the function used above.
This function is like below:
Function SumByColor (CellColor as Range, rRange as Range) Dim cSum As LongDim ColIndex as IntegerColIndex = CellColor.Interior.ColorIndexFor each cl in rRange if cl.Interior.ColorIndex = cSum then ColIndex = WorksheetFunction.SUM (cl, cSum) End IfNext clSumByColor = cSumEnd function
How to use this UDF:
Follow the steps below to use this function:
1. open your destination worksheet.
2. press ALT + F11 to open the VBA Editor and navigate to ' Insert ' > ' module '.
3. copy the "SumByColor" function in the Editor.
4. now, simply enter the "SumByColor" function to call him and pass the following arguments:
= SumByColor (
Note:In the formula above the image instead of the first argument "A2" we could also have used any of the A2, A5, A8, A10, A12. Because all these cells have a yellow background.
In our case we can use the following formulas:
Sum of yellow cells: = SumByColor (A2, A2: A13) [as ' A2 ' is the address of the cell yellow and A2: A13 is the range to be added]sum Orange cell: = SumByColor (A3, A2: A13) [as ' A3 ' is the address of the cell Orange and A2: A13 is the range to be added] sum of green cells: = SumByColor (A4, A2: A13) [as ' A2 ' is the address of the cell green and A2: A13 is the range to be added.
So, this was all from me on this thread. Don't forget to download the sample spreadsheet [link] ... and let me know if you experience problems when using these methods.
Credits to the post here
