How to Sum All Amounts Highlighted in a Colour on Calc Document?

[needcomputerhelp]

I have a calc spreadsheet I need help with. I’ve googled and googled and can’t find a solution, although that may be because I’m somewhat technologically inept.

I have highlighted (the background fill colour of the cell) monetary amounts according to a Key for a personal finance spreadsheet. For example, the cell containing daily amounts for expenditure on food is highlighted in red.
Rather than going through every red cell and adding up the amounts (i.e. =SUM(C76+812)) I would like to be able to do a sum where it adds up all of the cells which are highlighted in red.

I tried the advice on the following links:

LibreOffice Calc color-coding cells categorically

r/libreoffice - How can I make LibreOffice Calc sum only the cells with a...

Counting cells based on color

I have a calendar built, and I would like to be able to fill cells with a color and have a way to count the number of cells that have that color. So, in a defined range filled with, for example, red (representing vacation days) how can I sum all cells filled with red. This is helpful as I can slowly fill in all vacation days, holidays, then half or quarter vacation days, and quickly move them around and see where my total remains after different iterations. Thanks for your time.

Calc: sum cells values having same background color

In Calc I need to sum cells values only if the cells have the same background color. The colors are defined and identify persons Some cells may have the same value but different color (person) Here you can view an example of what I mean. Thank you for any hint.

Calc - SUMIF based on cell background color

Guys, I cannot think in a way to do this. I have a table with cells filled with green and red backgrounds, depending on other conditionals. How can I make a formula where it counts +1 if a cell is green, and ignores it if background is red? These cells already have values on them, but not what I need to sum up. Thanks in advance for any help.

I even tried (and failed) to create a ‘macro’ to help me do this (I’m not sure what a macro is). And now I don’t know how to remove the macro, so I’d also like help with this…

And I can’t get anything to work. I’m sure there must be a way around this so would really appreciate some help. Also if you could explain it very simply and give a step-by-step that very much appreciated!

I’m using LibreOffice version 6.3.2.2 on a Mac laptop, Operating system 10.13.6 and the document is .ods .

[anon73440385]

Though it is asked ever and ever again - it is the wrong approach to sum based on format properties of cells. There is probably a condition   why some cells are highlighted the way they are highlighted. And this condition needs to be used to calculate a sum (or whatever else).

You wrote you have a key defining your background colors. Use that key to sum using SUMIF() (and also use that key to conditional format your background colors)

[Lupp]

Using spreadsheets for the given kind of task:
Always use dedicated colums for entries supporting selections (for COUNTIF() or whatever function) and the like.
The entries made there can then also be referenced to additionally visualize the information using Conditional Formatting (CF) by colors e.g.
If you need help with transforming your sheet as it is to one as it should be (following my advice) come back with the respective question. I would actually need to provide a “macro” (user function) then, but I would refuse to do so just to support an insisting on the wrong way.

===Edit 2020-03-09 about 10:35 UTC===
The announced attachment is containing user code (“macro”).
To run it you should need to give your permission. Otherwise you have set ‘Low’ macro security what you never should do!
If you not are prompted for permission and the macro doesn’t run, you have set the macro security ‘High’ or ‘Very high’. You need to change it to ‘Medium’ then and to reload the file.
Generally you shouldn’t trust a source in the internet, and not run a macro without having checked it for probably contained malign code. In this case the macro has only 16 lines including the three above the function declaration:

REM  *****  BASIC  *****

Option VBAsupport 1

Function cellBackColorVba(pVBArange)
rg = pVBArange.CellRange
uR = rg.Rows.Count - 1
uC = rg.Columns.Count - 1
Dim backCol(uR, uC)
For r = 0 To uR
  For c = 0 To uC
    backCol(r, c) = rg.getCellByPosition(c, r).CellBackColor
  Next c
Next r
cellBackColorVba = backCol
End Function

Even unfamiliar with macros you should feel the absence of danger.

[needcomputerhelp]

@Lupp , I’m not really sure what you mean. Could you explain it more simply? I’m not very good with computers. Sorry.

[Lupp]

If I find the time I will try a more detailed explanation giving an example.
BTW: This is not a forum as you may know some (and I would prefer). It is a Q&A site. You shouldn’t use the ‘Answer’ option if you intend any clarification or question in return… You may edit your original posts where appropriate (without fogging the history, please) or add a comment to any post. A kind of ongoing discussion is only supported by the ‘Comment’ tool because answers are re-sorted depending on the votings they got. If equal insofar, the later answer is preferred.