Ask Your Question
0

Calc: Sum cells to the right of green cells

asked 2016-03-18 13:04:09 +0100

ctonkinson gravatar image

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.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2016-03-18 19:57:46 +0100

moshe11 gravatar image

updated 2016-03-18 19:59:38 +0100

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 .

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-03-18 13:04:09 +0100

Seen: 305 times

Last updated: Mar 18 '16