Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenFri, 18 Mar 2016 19:57:46 +0100Calc: Sum cells to the right of green cellshttps://ask.libreoffice.org/en/question/66785/calc-sum-cells-to-the-right-of-green-cells/I have a Calc spreadsheet, where I have columns of budget category *labels* and columns of budget *numbers*. I color categories green when it's something I want to pay for in cash, and so I'd like to get an automatic tally of how much cash I need to withdraw when the month begins.
I'm new to this O&A so I can't upload a screenshot, so here's a trivial example:
---------------------------
| Aaaa* | 10 | Eeee | 1 |
---------------------------
| Bbbb | 20 | Ffff* | 15 |
---------------------------
| Cccc* | 50 | | |
---------------------------
| Dddd | 25 | | |
---------------------------
(Imagine that the starred cells have a specific foreground color - in my case "Green 4" but I'm still learning Calc and haven't yet found the RGB/Hex for this).
---
What I'd like to do is get a `SUM()` for all cells *to the right of* a green cell. In this trivialized example, that would equal `75`. As in this example, not all of the cells will be in the same column, but they will all be in the same worksheet, and the number will always be in the cell directly to the right of the label.
How can I do this? I think this may be too complex for a normal Formula because of the scope and context sensitivity.Fri, 18 Mar 2016 13:04:09 +0100https://ask.libreoffice.org/en/question/66785/calc-sum-cells-to-the-right-of-green-cells/Answer by moshe11 for <p>I have a Calc spreadsheet, where I have columns of budget category <em>labels</em> and columns of budget <em>numbers</em>. I color categories green when it's something I want to pay for in cash, and so I'd like to get an automatic tally of how much cash I need to withdraw when the month begins.</p>
<p>I'm new to this O&A so I can't upload a screenshot, so here's a trivial example:</p>
<pre><code>---------------------------
| Aaaa* | 10 | Eeee | 1 |
---------------------------
| Bbbb | 20 | Ffff* | 15 |
---------------------------
| Cccc* | 50 | | |
---------------------------
| Dddd | 25 | | |
---------------------------
</code></pre>
<p>(Imagine that the starred cells have a specific foreground color - in my case "Green 4" but I'm still learning Calc and haven't yet found the RGB/Hex for this).</p>
<hr/>
<p>What I'd like to do is get a <code>SUM()</code> for all cells <em>to the right of</em> a green cell. In this trivialized example, that would equal <code>75</code>. As in this example, not all of the cells will be in the same column, but they will all be in the same worksheet, and the number will always be in the cell directly to the right of the label.</p>
<p>How can I do this? I think this may be too complex for a normal Formula because of the scope and context sensitivity.</p>
https://ask.libreoffice.org/en/question/66785/calc-sum-cells-to-the-right-of-green-cells/?answer=66815#post-id-66815There are a few ways to solve this .
Let's start with making sense with your data .
First put your data in 3 columns .
The first column will be NAME (e.g. books,food.parking) .
The second column will be AMOUNT (e.g. 10,19.9,102) .
And the last column will be CRITERIA (e.g. cash,CreditCard,SexualFavours) .
Now , you use the function SUMIF that sums up all the numbers in the AMOUNT ,
if they match your CRITERIA .
Of course you can sumif in a few cells ,
where each cell sums according to a different criteria .
If you really need to spread your data in many columns ,
or , if you must depend on cell colours ...
Well , there are other ways to solve this .Fri, 18 Mar 2016 19:57:46 +0100https://ask.libreoffice.org/en/question/66785/calc-sum-cells-to-the-right-of-green-cells/?answer=66815#post-id-66815