Calc: Sum cells to the right of green cells

[ctonkinson]

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.

[moshe11]

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