Summing Filled Cells

Tiffany has a column that contains numeric values. Some of the cells have been filled with different colors. Some of those colors are explicit; some are applied through conditional formatting. She would like to be able to sum the cells that contain any fill color, regardless of the color. In other words, the sum should exclude only the cells that have no fill color.

One way to accomplish this task is by using the filtering capabilities of Excel. For instance, you could follow these steps:

1. Select a cell in your data column.
2. Display the Data tab of the ribbon.
3. Click the Filter tool. Excel places drop-down arrows in the first cell of the column.
4. Click the drop-down arrow. Excel displays a palette of filtering options.
5. Click the arrow at the right of the By Color field. Excel displays three options: Fill Color, Cell Color, and Cell Icon.
6. Hover the mouse pointer over the Fill Color option. Excel displays a list of the colors used in the column.
7. Choose the No Fill option. Excel immediately filters the data so that only those rows with no fill color in that column are displayed.
8. In a different cell, enter the following formula:

=SUM(A2:A99)-SUBTOTAL(9,A2:A99)

The value returned from the formula is the sum of all the cells that use any fill color. This works because the SUM function returns of sum of all the cells in the range (in this case, A2:A99), and the SUBTOTAL function returns the sum of all the visible cells in the range. Subtract the visible (non-filled) cells from the total for the range, and you end up with the sum of all filled cells.

If you would prefer to not filter your data, then you could create a user-defined function to return the sum. The following would work great for this purpose:

Public Function SumFilled(Target As Range)
    Dim cell As Range
    Dim sAddr As String

    Application.Volatile
    For Each cell In Target
        sAddr = cell.Address(External:=True)
        If Evaluate("CellHasFill(""" & sAddr & """)") Then
            SumFilled = SumFilled + WorksheetFunction.Sum(cell)
        End If
    Next cell
End Function

Private Function CellHasFill(Addr As String) As Boolean
    CellHasFill = (Range(Addr).DisplayFormat.Interior.ColorIndex <> xlColorIndexNone)
End Function

Note that there is a UDF defined here (SumFilled) and a function that is called from the UDF. This arrangement is necessary because the heart of the macro is the .DisplayFormat.Interior.ColorIndex property which returns the color that is displayed in a cell, regardless of whether it is the result of an explicit fill or of a conditional format. This property—most likely because of a quirk (i.e., bug) in Excel—won't work directly in a UDF, though it will work in a hard-coded (non-UDF) macro. For more information on this and why this workaround approach was used, see the following thread at SubStack:

https://stackoverflow.com/questions/52265819/getting-cell-interior-color-fails-when-range-passed-from-worksheet-function/

Thus, to use the UDF you could put the following in your worksheet:

=SumFilled(AA2:A99)

If you want to use a non-UDF macro, then the following will work:

Public Sub ClrSum()
    Dim x As Double
    
    Set Rng = Range("A2:A99")
    x = 0
    For Each c In Rng.Cells
        If c.DisplayFormat.Interior.ColorIndex <> xlColorIndexNone Then x = x + c.Value
    Next c
    MsgBox x
End Sub

Run the macro, and it evaluates all the cells in the range A2:A99 and returns the sum displayed in a message box.

Finally, it should be noted that if you have the opportunity to rethink the organization of your worksheet, it would be advisable to not use color as data. In other words, you might consider adding a column to contain a numeric indicator as to the status of the values in your presently color-coded values. In this way you could easily use functions such as SUMIF to evaluate your data. If you want the color coding, then you could create conditional formatting that would modify color based on the value in the added status column.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9859) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.