How To Add Data in Colored Cells in Microsoft Excel

The use of the SUM formula is common to add a range of cells. You can also use SUMIF as well if some condition has to be fulfilled for the addition of certain cells. But if we need to add certain cells from a range on basis of the cell background color or font color of those cells, Excel doesn't have any built-in solution so far.

One way to fulfill the above requirement is to write a user-defined function. We will write a simple function that will help us use a custom formula to add the content in colored cells.

You will 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
 Next
 SumIfColor = SumIfColor
 End Function

After you have pasted the above code, as an easy example, we have below data where highlighted in green that are to be added. We must have a cell where the required color is fixed 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 the formula result. The result of the formula does not get updates by F9 or even by reopening your Excel file.