[Solved] Calc formula help

[elaterite]

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!

[robleyd]

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.

[elaterite]

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]

Marked solved. Thanks.

[anon87010807]

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