Use of SUM formula is very common to add a range of cells. You can also use SUMIF as well if some condition has to be fulfilled for addition of certain cells. But if we have to add certain cells from a range on basis of cell background color or font color of those cells, Excel doesn’t has any built-in solution so far.
Only way to fulfill above requirement is to write a user defined function. We will write a simple function which will help us use a custom formula to add content of colored cells.
You will have to open VB Editor (short key is ALT+F11) and insert new module there. Simply paste below code inside new module.
Function SumIfColor(sumRange As Range, requiredColor As Range) As Variant
Dim dataCell As Range
For Each dataCell In sumRange
If dataCell.Interior.ColorIndex = requiredColor.Interior.ColorIndex Then
SumIfColor = dataCell + SumIfColor
SumIfColor = SumIfColor
After you have pasted above code, as an easy example, we have below data where highlighted in green are to be added. We must have a cell where required color is fixed so as to compare cells from range.
We are going to add colored cells from E1 to E4. Our required color is in D5. To have our sum result in E5, simple write down above function name SumIfColor as formula like =SumIfColor(range of cells to be added , cell containing required color)
Formula in our example will be =SumIfColor(E1:E4,D5)
Please Note that whenever you change the color of any cell, you have to press CTRL+ALT+F9 to update formula result. Result of the formula doest get updates by F9 or even by reopening your Excel file.