[CALC] Function sum by color

[filo62]

Hello, I wish to write a function which makes the sum of the cell whose backcolor is blue
example : sumcolor(A1:A5,255). attention A1:A5 is not a selection by mouse and not string

I wish to be able to stretch the cells to recopy the formula. And that it remakes calculations automatically like the function "sum"

Thanks for you help

[ChrisK]

What about something like this:

[kumkum]

Hi,

I would like to start using calc instead of excel, but I despertly need to convert excel function. the function performs the following:

' returns the sum of each cell in the range InputRange that has the same
' background color as the cell in ColorRange
' example: =SumByColor($A$1:$A$20,B1)
' range A1:A20 is the range you want to sum
' range B1 is a cell with the background color you want to sum

Can you help please ?

Tx

[pitonyak]

[Villeroy]

I would use conditional formatting or function STYLE() in order to paint certain cells with a color and then get the sum from the same criteria.
If your colors are hard formatted by some user's fuzzy logic (they don't match calculable criteria), you can get this feature within a minute:
Install this module http://www.oooforum.org/forum/viewtopic.phtml?t=32909
and add the following function:

[SacredCow]

Greetings All,

I have a spreadsheet that I'm migrating from Excel that had a macro to SumByColor whereby it would sum the contents of a column based on those fields with red numbers. I've attached the code below and while I imagine it may not work with OOo due to VBA I thought maybe there was a similar solution available in OOo?

Thank you for any assistance you can offer.

Sincerely,
-SC

[Villeroy]

Did you try the above CELL_BACKCOLOR function?
Why not?
Have you ever used a spreadsheet without the help of other people's VBA code?
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

[SacredCow]

No I did not try the backcolor. Why not? Because a) I'm asking about FONT color and not cell back color, b) looking at the code this would not meet my needs. I'll be working with pairs of rows of data whereby one row of the pair will be marked with a color, however, only those cells with a value should be part of the sum. In your case every cell within the selected row would wind up being totaled in the sum.

[Villeroy]

Another programmer who does not program.
The variable inspector in the Basic IDE as well as any of the frequently mentioned helper tools show you all the properties of a cell within seconds.
A simple stop mark in the Basic IDE reveals instantly how the function is supposed to work.
Simply copy the function, rename it, replace "CellBackColor" with "CharColor" and you have your function. Same trivial replacement for any other cell property.

[lrey1154]

I'm actually responding to an assignment where it looks like font color needs to be tested.

I'm new to OO macros so bear with me.

In one case the test for font color seems to work but in other it doesn't.

This case does not seem to check color, also used numeric coded as in following code that does seem to verify font color test. Text value passes but there is a cell with white font color that isn't counted.

For iRow = LBound(x, 1) To UBound(x, 1)
For iCol = LBound(x, 2) To UBound(x, 2)
oCell = Sheet.getCellByPosition(iRow, iCol)
If x(iRow, iCol) = "XXXXX" Then
If oCell.CharColor = rgb (255,255,255) Then
XXXXXSumval = XXXXXSumval + 1
End If
End If
Next
Next



This code does seem to perform color test

For iRow = LBound(x, 1) To UBound(x, 1)
For iCol = LBound(x, 2) To UBound(x, 2)
oCell = Sheet.getCellByPosition(iRow, iCol)
If x(iRow, iCol) = "YYYYY" Then
If oCell.CharColor = 16777215 Then
YYYYYSumval = YYYYYSumval + 1
End If
End If
Next
Next

Initially had nested ifs in single function then broke up into individual functions with same result.