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

[Bikermagi]

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;
}

Hmmm.

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

[LeroyG]

See an old question: Calc: sum cells values having same background color

[Zizi64]

“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.

[doak]

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.

Is that true?
I found this solution to get the absolute address of a cell passed by a range (afaik):

Option VBAsupport 1

function addressOfRange(oRange as range) as string
	 addressOfRange = oRange.getCellrange().AbsoluteName
end function

[Lupp]

VBAsupport is “work in propgress”. I don’t know any clear explanation telling what works reliably and what does not.
Using the ways of LibreOffice API to provide fundamental means of processing, the relevant service is ooo.vba.excel.Range: I can’t find any specification for it.
Thus (based on my own experience): It’s possible to pass a simple range or a ParamArray. What else will work, I don’t know. And I also don’t know what unresolved conflicts may occur.
Just one example concerning the given context: A passed A-Range will give access to a .CellRange property, but this object will not support the com.sun.star.sheet.SheetCell service as ranges created the orinary LibO way would do.
In short: If you generally want to do get things done the Excel-way, use Excel.

Also regard my older comments below and this example:
nonsenseDemo.ods (16.4 KB)

[Zizi64]

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.

[Lupp]

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 user-code for the purpose is bound to be rather inefficient. Detailed reasons on request.)

[Lupp]

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.

[igorlius]

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!

[Bikermagi]

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

[thirty-thousand]

I am using Libra office for Mac

LibreOffice_7.5.3_MacOS_x86-64.

What I do is format the colors I want, in the cells I want, by holding the “command” key and clicking each cell. Then right click to format, Then select my color, IE: “Lime Green 4” Now I select the cell I want the sum to appear in ,and simply go through the same selecting process by holding the “command” key and selecting the cells which are lime green .