Ask Your Question
0

[Solved] Calc formula help

asked 2017-04-10 02:58:26 +0200

elaterite gravatar image

updated 2017-04-10 06:20:49 +0200

Hi -

I've never done anything other than add (sum) a single column in Libreoffice Calc.

So now I have developed an accounting spreadsheet for my small business.

I need a formula that will add (sum) only certain cells in one column, while excluding cells in that same column if there's a number greater than zero in a cell in an adjacent column.

Example: I need the sum of cells E12-15, except there's a number greater than zero in G12 so E12 either needs to be ignored by the formula or subtracted from the sum. Likewise, on the same sheet, but in a different category, I need a sum for E18-21, however, there's a number greater than zero in G19 so the number in E19 needs to be ignored or subtracted.

Of course, I'd like the formula to be expandable as I add additional data to the different categories. In the two above examples, for instance, if I add data to E16, 17..., or E22, 23.... Of course, one category may expand into where a previous category exists as I add rows of data.

Can anyone help me with a formula for this and how I make the formula specific, and expandable, to a specific category of cells?

Thanks!

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
1

answered 2017-04-10 03:07:34 +0200

robleyd gravatar image

updated 2017-04-10 06:30:07 +0200

SUMIF() is probably the function you need. Look it up in the Help - press F1 and search sumif

Update: A combination of SUMIF() and named ranges might be your solution. I've uploaded a sample file with a couple of comments that may help you.

sumif_ranges.ods

If this answer helped you, please accept it by clicking the check mark to the left and, karma permitting, upvote it. If this resolves your problem, close the question, that will help other people with the same question.

edit flag offensive delete link more

Comments

Okay, thanks, that helped, robleyed. I just realized how to do what I need to do. Sum the total of the E cells and G cells separately and then subtract the G subtotal from the E subtotal.

Now I just need to figure out how to apply the function to separate categories in a single column and then get a sum of the separate of the subtotals.

elaterite gravatar imageelaterite ( 2017-04-10 04:43:13 +0200 )edit

Marked solved. Thanks.

elaterite gravatar imageelaterite ( 2017-04-10 06:21:54 +0200 )edit

You mark as Solved by ticking the ✔ mark to the left of the answer.

floris v gravatar imagefloris v ( 2017-04-10 20:33:51 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-04-10 02:58:26 +0200

Seen: 202 times

Last updated: Apr 10 '17