Ask Your Question

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

asked 2020-08-18 22:13:54 +0100

Bikermagi gravatar image

updated 2020-08-19 03:54:16 +0100

Is it possible to selectively sum only those cells in a range where the data color property is set to a certain color ( I have used Red3 and Green3 FWIW )

I have mused over a function to do this, I'm not a spreadsheet programmer, and I do not want to reinvent the wheel, but acting selectively on a selection does not seem to be something weird, I just cannot find an existing answer.

I thought about some pseudocode.

SumQualifiedRange ( the_range, the_color ) { Int nSum, nrlow, nrhigh, nColumn, nRow; nrlow = range_low(the_range); nrhigh = range_low(the_range); nSum=0; nColumn=column(the_range); For nRow=nrlow to nrhigh { If cell( nRow, nColumn ).properties.color = the_color { nSum=nSum+ cell( nRow, nColumn).value; } } return nSum; }


SUMIF() woulod be good if we could test the color there.

edit retag flag offensive close merge delete


LeroyG gravatar imageLeroyG ( 2020-08-18 22:30:55 +0100 )edit

"SumQualifiedRange ( the_range, the_color )" It is not possible to pass a cell range object to a Basic function. Only the content of the selected cellrange will be passed with ByVal method, but will not passed the other properties of the range like the absolot coordinates or formatting properties. You can pass:

  • The name of the cellrange as a string value (with or without the name of the sheet).
  • The value array from a cell range.

You must get the specific cellrange by your macro based on these values. Then you will able to examine the formatting properties of the individual cells in the cell range.

Zizi64 gravatar imageZizi64 ( 2020-08-19 11:50:24 +0100 )edit

2 Answers

Sort by » oldest newest most voted

answered 2020-08-19 08:00:59 +0100

Zizi64 gravatar image

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.
edit flag offensive delete link more


The problem isn't to find/write an introspective function needed to get the .CellBackColor from a cell, and then to be able to compare it with a given RGB-value. If this was everything to the subject, it would even be acceptable, to use such colors as a kind of data. The imoportant and unavoidable disadvantages are:
-1- RGB-color-values are 24 bit. There are 16 777 216 different colors. Many of them look the same to humans. Each one may look very different to different people, or depending on whatever is used for the visualization. There is no way to tell which RGB-values belong to what we think is "red", e.g, except for an arbitrary definition.
-2- Palettes cannot help, because they are arbitrary again, and subject to changes. Even the same application may name different RGB-values "red" if it uses different palettes depending on the context.
-3- Introspective ...(more)

Lupp gravatar imageLupp ( 2020-08-23 13:07:23 +0100 )edit

The aspects @Zizi64 pointed to are very relevant, and concerning the inefficiency it wouldn't help to use a CellStyle, even if conflicting direct attributes are avoided.
Therefore use introspective functions if unavoidably needed because you already have sheets coding badly for some data using colors (or any attributes), but use them only to once-and-for-all convert these badly coded data to real (explicit) ones using their own column.

Lupp gravatar imageLupp ( 2020-08-23 13:13:20 +0100 )edit

answered 2020-08-20 10:27:25 +0100

igorlius gravatar image

updated 2020-08-23 19:49:21 +0100


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

    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

    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
        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!

edit flag offensive delete link more



Thank you ... I will see where that gets me. Much appreciated.

Bikermagi gravatar imageBikermagi ( 2020-08-29 06:11:58 +0100 )edit
Login/Signup to Answer

Question Tools



Asked: 2020-08-18 22:13:54 +0100

Seen: 132 times

Last updated: Aug 23 '20