How to sum() only cells where certain color is set

edit

There are workarounds (custom macros) for this task, but it is better to avoid from use the formatting properties as some "data". You can format the cell based on a real data by the Conditional Formatting feature (this is the right direction), but never try to use the formatting property as a condititon for a calculation.

Here are some reasons - for example:

• There is no way to get the actual background color of a conditionally formatted cell.
• And sometimes the background colors (applied by a cell style) will be overwrited by manually (directly) applied background color.

Hello,

the following macro (see. end of post) does what you want. Just put it into Tools -> Macros -> "Edit Macros" and you can call it from inside any cell via

=SUMIFCOLOR("A1:A5", "ff0000")

• The first argument is the range quoted as a string
• The second argument is a hexadecimal color value quoted as a string To view or set the hex value of a cell open the color picker ref1. ref2.

Here the macro function:

Function SUMIFCOLOR(range As String, hexColor As String)

    range = Trim(range)
    hexColor = UCase(Trim(hexColor))

    Dim sum As Double
    Dim i As Integer
    Dim j As Integer
    Dim decColor As Long

    if (Len(hexColor) <> 6) then
        SUMIFCOLOR = "ERR: invalid hex code, length <> 6"
        Exit Function
    endif

    For i = 1 To Len(hexColor)
        c = Mid(hexColor, i, 1) 
        Select Case c
            Case "0","1","2","3","4","5","6","7","8","9","A","B","C","D","E","F"
            Case Else
            SUMIFCOLOR = "ERR: invalid hex code, invalid hex char"
            Exit Function
        End Select
    Next

    REM convert hex to long 
    decColor = CLng("&H" & hexColor) 

    sum = 0.0

    Set oSheet = ThisComponent.CurrentController.ActiveSheet
    Set oRange = oSheet.getCellRangeByName(range)

    For i = 0 To oRange.Rows.getCount() - 1             
        For j = 0 To oRange.Columns.getCount() - 1
            Set oCell = oRange.getCellByPosition( j, i )
                if (oCell.CellBackColor = decColor) then
                    sum = sum + oCell.Value
                endif
        Next j
    Next i 
    SUMIFCOLOR = sum
End Function

Hope that helps.

To show the community your question has been answered, click the ✓ next to the correct answer, and "upvote" by clicking on the ^ arrow of any helpful answers. These are the mechanisms for communicating the quality of the Q&A on this site. Thanks!