Sum Cells based on Background Color
SUM cells on the basis of background colour using SUMIF Formula:
first of all let’s try to understand how we are going to do this:
Consider we have a table as shown in the below image.
Next, we will add one more column to this table where we will manually type the background colors of their adjacent cells as shown in the below image.
Now, we try to use SUMIF Formula for finding the SUM of cells with yellow background as:
Similarly, for finding the SUM of Orange and Green background cells we will use the formulas
But, as we can see that this method is quite cumbersome, particularly if we need to use this on lists with hundreds of elements.
So, what’s the faster alternative?
To make the above process easier to use we need to reduce the effort of writing 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 = CellColor.Interior.ColorIndex
- Function ColorIndex(CellColor As Range)
- ColorIndex = CellColor.Interior.ColorIndex
- End Function
Note: This function does not return the color name but it returns the color index which is also a unique value and can be used in our task.
Follow the below steps to use the UDF:
1. First of all open your worksheet where you need to add the cells based on background colors.
2. Next, press ALT + F11 to open the VB Editior. Navigate to ‘Insert’ > ‘Module’.
3. After this, paste the “ColorIndex” UDF in the Editor.
4. Now, add one column next to the range that you wish to sum up. In this new column enter the formula as:
5. After that, drag this formula to the whole range.
6. Now, you can use the SUMIF function to add the cells that have same background color as shown in the above image.
Method 2 : Using a much faster and better UDF:
The UDF that we are going to use in this method is simply an extension of the above used function.
This Function is as under:
Dim cSum As Long
Dim ColIndex As Integer
ColIndex = CellColor.Interior.ColorIndex
For Each cl In rRange
If cl.Interior.ColorIndex = ColIndex Then
cSum = WorksheetFunction.SUM(cl, cSum)
SumByColor = cSum
- Function SumByColor(CellColor As Range, rRange As Range)
- Dim cSum As Long
- Dim ColIndex As Integer
- ColIndex = CellColor.Interior.ColorIndex
- For Each cl In rRange
- If cl.Interior.ColorIndex = ColIndex Then
- cSum = WorksheetFunction.SUM(cl, cSum)
- End If
- Next cl
- SumByColor = cSum
- End Function
How to use this UDF:
Follow the below steps to use this Function:
1. Open your target worksheet.
2. Press ALT + F11 to open the VBA Editor and navigate to ‘Insert’ > ‘Module’.
3. Paste the “SumByColor” Function in the Editor.
4. Now, simply type the “SumByColor” function to call it and pass the following arguments:
Note:In the formula shown in above image instead of the first argument “A2” we could have also used any one of A2, A5, A8, A10, A12. Because all these cells have 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 yellow cell and A2: A13 is the range to be added]
- Sum of Orange Cells:
=SumByColor(A3,A2:A13)[As ‘A3’ is the address of orange cell and A2: A13 is the range to be added]
- Sum of Green Cells:
=SumByColor(A4,A2:A13)[As ‘A2’ is the address of green cell and A2: A13 is the range to be added]
So, this was all from me about this topic. Don’t forget to download the sample spreadsheet and do let me know in case you face any issues while using these methods.
Source : http://www.exceltrick.com/how_to/sum-cells-based-on-background-color/