How To Add Colored Cells in Microsoft Excel

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
End If
SumIfColor = SumIfColor
End Function

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.

Use of Custom Formula

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.

Receive Excel Tips and Functions in your email

Sign up for our Newsletter -provide your email address to receive updates in your inbox.

Speak Your Mind