OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Display breakdown of items based on category

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
hop-frog
Power User
Power User


Joined: 22 Jul 2004
Posts: 74

PostPosted: Sat Jun 03, 2006 10:03 pm    Post subject: Display breakdown of items based on category Reply with quote

Hello,

I am creating a simple spreadsheet in Calc for maintaining a balance on multiple accounts, set up just like a checkbook, but with the addition of a breakdown of the information displayed on the second page (Sheet2) for the purpose of maintaining a budget.

A very simplified version of the balance sheet's column headings on Sheet1 follows:

Date | Payee | Category | Amount

Rows represent each transaction.

Now, lets say I have recorded the following transactions:

06/01/2006 | Wal-Mart | Groceries | $16.09
06/02/2006 | Olde Towne Bakery | Groceries | $2.15
06/03/2006 | Wal-Mart | Entertainment | $17.81

All of the above, I know how to accomplish. The resulting breakdown which would appear in Sheet2, however, I am not sure how to make, nor do I know if it is possible in Calc:

Amount spent on Groceries: $18.24
Amount spent on Entertainment: $17.81

Amount spent at Wal-Mart: $33.90
Amount spent at Olde Towne Bakery: $2.15

Somehow, it has to go through and search for all transactions categorized under Groceries and then display the sum total of all of just these transactions.
Back to top
View user's profile Send private message
noranthon
Super User
Super User


Joined: 07 Jul 2005
Posts: 3318

PostPosted: Sun Jun 04, 2006 4:18 am    Post subject: Reply with quote

The SUMPRODUCT function will do what you want.
_________________
search forum by month
Back to top
View user's profile Send private message
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Sun Jun 04, 2006 4:46 am    Post subject: Re: Display breakdown of items based on category Reply with quote

hop-frog wrote:

All of the above, I know how to accomplish. The resulting breakdown which would appear in Sheet2, however, I am not sure how to make, nor do I know if it is possible in Calc:

Amount spent on Groceries: $18.24
Amount spent on Entertainment: $17.81

Amount spent at Wal-Mart: $33.90
Amount spent at Olde Towne Bakery: $2.15



Something like this:

=SUMIF($C$1:$C$8;"Groceries";$D$1:$D$8)

Similarly for the second set.

David.
Back to top
View user's profile Send private message
hop-frog
Power User
Power User


Joined: 22 Jul 2004
Posts: 74

PostPosted: Sun Jun 04, 2006 6:33 am    Post subject: Reply with quote

Thanks!
Back to top
View user's profile Send private message
Nalo
General User
General User


Joined: 29 Jan 2006
Posts: 7

PostPosted: Thu Jun 08, 2006 2:02 pm    Post subject: Re: Display breakdown of items based on category Reply with quote

Hello,

I'm working on a similar spreadsheed and I have another similar question:

How can I get the amount spent on Groceries at Wal-Mart? Or how can I get the amount spent on Entertainment at Wal-Mart?

The problem is how to specify two conditions (over two columns) in =SUMIF( $C$1:$C$8;"Groceries";$D$1:$D$8 ) .

Thanks
_________________
----------------
Nalo
Back to top
View user's profile Send private message
Nalo
General User
General User


Joined: 29 Jan 2006
Posts: 7

PostPosted: Thu Jun 08, 2006 2:32 pm    Post subject: Re: Display breakdown of items based on category Reply with quote

Hello,

I have just found the solution!

=SUMPRODUCT( $C$1:$C$8="Groceries";$B$1:$B$8="Wal-Mart";$D$1:$D$8 )

Sorry for beg you with the question and the answer, but I think this will help somebody.

Regards,
_________________
----------------
Nalo
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu Jun 08, 2006 2:32 pm    Post subject: Re: Display breakdown of items based on category Reply with quote

hop-frog wrote:
Hello,
Date | Payee | Category | Amount
06/01/2006 | Wal-Mart | Groceries | $16.09
06/02/2006 | Olde Towne Bakery | Groceries | $2.15
06/03/2006 | Wal-Mart | Entertainment | $17.81

Select entire columns A to D
Menu:Data>Data Pilot>Start...
Confirm "Current Selection"
Hit "More..."
Choose target cell sheet2.A1 (or any other top left cell of a unused range)
Check al other options below
Drag "Amount" to "Data Fields"
Drag "Category" and "Payee" to "Column Fields"
Drag "Date" to "Row Fields"
Confirm dialog
Select a single Date
Menu:Data>Outline>Group
Check "Months" and "Years"
On the sheet drag around the grey boxes in order to remove a field or change a field's orientation.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group