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

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

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)

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.

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.

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

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.