[Solved] Total expenditure (Amount) for each of the category

Discuss the spreadsheet application

[Solved] Total expenditure (Amount) for each of the category

Postby Mark Bulgaria » Thu Dec 05, 2019 3:14 pm

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
Last edited by Hagar Delest on Fri Dec 06, 2019 11:58 pm, edited 2 times in total.
Reason: tagged solved
OpenOffice 3.1 on Windows Vista
Mark Bulgaria
 
Posts: 2
Joined: Thu Dec 05, 2019 1:21 pm

Re: Formulas

Postby Zizi64 » Thu Dec 05, 2019 4:28 pm

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.
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.3.4; AOO4.1.6
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 8672
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Formulas

Postby John_Ha » Thu Dec 05, 2019 5:25 pm

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.

Code: Select all   Expand viewCollapse view
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

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.

Clipboard02.gif

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.
AOO 4.1.6, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
John_Ha
Volunteer
 
Posts: 7164
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: Total expenditure (Amount) for each of the category

Postby Villeroy » Fri Dec 06, 2019 2:11 am

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27599
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Total expenditure (Amount) for each of the category

Postby Mark Bulgaria » Fri Dec 06, 2019 10:37 am

Thank you everyone for their help and support
OpenOffice 3.1 on Windows Vista
Mark Bulgaria
 
Posts: 2
Joined: Thu Dec 05, 2019 1:21 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 8 guests