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

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.

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.

edit retag close merge delete

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

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

Sort by » oldest newest most voted

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

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

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

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!

more

1

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

( 2020-08-29 06:11:58 +0100 )edit

## Stats

Seen: 129 times

Last updated: Aug 23 '20