[Solved] SUMs from multiple sheets filtered by category

Discuss the spreadsheet application

[Solved] SUMs from multiple sheets filtered by category

Postby yoyopoc » Wed Apr 17, 2019 1:21 pm

Hi everyone,
Forgive me but I am an artist and have little knowledge of spreadsheets, my problem is.
I'm trying to get yearly sum totals from my monthly expenses from my openoffice spreadsheet, 12 sheets, 1 for each month and I need to create a master sheet with totals for each of my expenses categories, for example "Insurance" which is a category in my expenses categories in column G and its monetary value is held in column D. I've searched VLOOKUP in a hope of finding my way but as you can see from this example"=VLOOKUP(SHEET(april);AREAS(april.A2:G27);MATCH("Insurance" ))" I am hopeless, I'm intellegent enough to be able to move forward through all my yearly totals if I can only get one category sumtotal to work, help :shock:
Last edited by Hagar Delest on Wed Apr 17, 2019 9:31 pm, edited 2 times in total.
Reason: tagged solved
OpenOffice 4.1.1, MAC OS 10.13.6
yoyopoc
 
Posts: 5
Joined: Wed Apr 17, 2019 12:59 pm

Re: SUM totals from multiple sheets filtered by category

Postby FJCC » Wed Apr 17, 2019 4:42 pm

You can use the SUMIF() function. Something like
Code: Select all   Expand viewCollapse view
=SUMIF(Sheet1.G1:G100;"Insurance";Sheet1.D1:D100)

I also suggest that you not spread your data over 12 sheets. That makes calculations like this more difficult. Humans like data organized on different sheets but computers like it all in one place.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7260
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: SUM totals from multiple sheets filtered by category

Postby yoyopoc » Wed Apr 17, 2019 9:02 pm

Many thanks for your help FJCC this works perfectly, I'll copy all entries into a single sheet and weed out the human touch in future again many thanks from a novice. :bravo:
OpenOffice 4.1.1, MAC OS 10.13.6
yoyopoc
 
Posts: 5
Joined: Wed Apr 17, 2019 12:59 pm

Re: [Solved] SUMs from multiple sheets filtered by category

Postby robleyd » Thu Apr 18, 2019 12:45 am

Once you have organised your data into a single sheet you can also use Pivot Tables to summarise your data.
Cheers
David
Apache OpenOffice Developer Build 4.2.0 9820 - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 2941
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: [Solved] SUMs from multiple sheets filtered by category

Postby yoyopoc » Thu Apr 18, 2019 12:49 pm

Hi robleyd,
Thanks for the pointer, I'll look into pivot table tutorials.
Thanks for your help.
Cheers :D
OpenOffice 4.1.1, MAC OS 10.13.6
yoyopoc
 
Posts: 5
Joined: Wed Apr 17, 2019 12:59 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 42 guests