Apache OpenOffice Community Forum

Date        Income/Expenses  Category       Memo         Amount  
2019-11-29  Expenses         Food           kaufland     -15.85  
2019-11-28  Expenses         Food           yanna        -52.15  
2019-11-27  Expenses         Car            petrol       -40     
2019-11-27  Expenses         Entertainment  Starbucks    -12.2   
2019-11-27  Expenses         Shopping       lidal Sonia  -40.02  
2019-11-27  Expenses         Clothing       belt         -45     
2019-11-27  Expenses         Entertainment  McDonald's   -18.3   
2019-11-27  Expenses         Entertainment  Happy        -109.42 
2019-11-25  Expenses         Food           yanna        -32     
2019-11-25  Expenses         Health         chemist      -38.06

These are some of the figures from a spreadsheet and what I would like to do is add up all the different category's and have one total expenditure (Amount) for each of the category's is there a method to do this and would be possible to have a sample?

Thank you in advance.

Mark

You can use the function SUMIF() - if the date, the category and the value are not located in same cell.

Please upload a real, ODF type sample file here - instead of the textual sample.

Zizi64 wrote:You can use the function SUMIF() - if the date, the category and the value are not located in same cell

I think you will find that the whole concept of doing it like this is wrong and will lead to endless problems because if you use the text content of a cell to take a decision you must ensure that your spelling always correct and always identical - even two spaces instead of one will cause a search based on the text to be wrong. An example? You appear to have spelled Lidl as lidal. yanna and kaufland are not capitalised - Starbucks and Sonia are.
It is infinitely better to place the values in a column for that budget category - that way you know it is correct and can merely sum each column.

Better is to learn how to use Form Controls (check Help) so you can select from a drop-down list box which lists all the categories, income/expense etc, and just leave Memo as a free text area. A half-way method is a Select from list as in the image below.
I also think you will also get very confused by setting expenditure as a negative number - you should set it as a positive number. The fact that it is expenditure means you subtract it from your income or from your running balance. As it is you will have to add your negative expenditure to your income to get what you have saved. This is the classic error of having the same information (Expenditure and -ve) stored twice in two different places - in time they will get changed and no longer agree.

I strongly suggest you learn a little more about spreadsheets before starting again. See [Tutorial] Ten concepts that every Calc user should know and the Calc Guide.

See 15 Excel Spreadsheet Templates for Managing Your Finances

Better still, use a database with a form to enter each transaction and and create multiple reports on the entered data. This separates data entry and analysis and protects the data from accidental change.

Even better get a free or pay for package to do family finances.

Pivot tables (aka data pilots) are easy.
download/file.php?id=38070
https://wiki.openoffice.org/wiki/Docume ... /DataPilot

Thank you everyone for their help and support

I'm sorry but I don't see a formula other than =SUMIF(). That's fine if you know the format. I have a file I could use this on and would like to upload it but don't know how

I have a file I could use this on and would like to upload it but don't know how

Use the Post Reply (but not the Quick Reply), and then use the Attachment TAB under the message editor window.
Tha maximal file size is 128 kiB in this forum.

After sending this request I continued working on it and found that instead of using " " I was using ' '. It's working great now so thanks for the information because I am sure I will need it in the future