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

Use Names to sort lists

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


Joined: 02 Sep 2007
Posts: 11

PostPosted: Sun Sep 02, 2007 10:02 am    Post subject: Use Names to sort lists Reply with quote

I keep track of my business in Calc, with a book I created for this, but lately I have found myself wanting more... My first column lists the date of a purchase, my second column lists the supplier I bought stuff from, the 3rd and 5th columns show the amount and whether its materials or tools. The columns can get quite long on some jobs, and I want to set up a new area in each sheet that will tally, (for instance), all the 'Home Depot' purchases, all the 'Genesee Lumber' purchases, and so on. This way I can have an instant view of where I stand at any given time. But I want Calc to search thru that 2nd column and find the different suppliers by name as they get typed in randomly, except for date. If its easier to view, I can send along a portion of the sheet for clarity. How do I instruct the software to sort the 2nd column by name and then sum the 3rd and 5th columns in those rows to a different cell?

Thanks
Jackie
Back to top
View user's profile Send private message
RickRandom
Super User
Super User


Joined: 27 Jan 2006
Posts: 1082
Location: UK

PostPosted: Sun Sep 02, 2007 10:53 am    Post subject: Reply with quote

You can highlight all the data, then from the menu, Data, Sort, then select which columns to sort by. However if the only reason you want to sort is to total all your purchases from one place, you might not need to sort, perhaps just use the SUMIF function, something like:

=SUMIF(range with Home Depot or other in it;"Home Depot";range with amounts in it)

You need to be certain that you are consistent with what you put in, e.g. "Home Depto" won't be included in the SUMIF.

Let us know how you get on.
Back to top
View user's profile Send private message
JackieBaby
General User
General User


Joined: 02 Sep 2007
Posts: 11

PostPosted: Sun Sep 02, 2007 6:09 pm    Post subject: Reply with quote

I did something wrong, or else I just don't get it. There are many 'Home Depot' entries, many 'Genesee Lumber' entries, etc. I am trying to get Calc to sum ALL the costs from each row that Home Depot is found in. Like wise for several other suppliers.

Thanks
Jackie
Back to top
View user's profile Send private message
RickRandom
Super User
Super User


Joined: 27 Jan 2006
Posts: 1082
Location: UK

PostPosted: Sun Sep 02, 2007 9:07 pm    Post subject: Reply with quote

Try summing one columns at a time, e.g.

=SUMIF(B1:B100;"Home Depot";C1:C100)+SUMIF(C1:C100;"Home Depot";E1:E100)

assuming supplier in column B, amounts in columns C and E. Asjust ranges as necessary.

If this doesn't work, please copy the exact formula you used into your reply, and detail the results you get. Got to go to work now. I will check back later.

By the way, another possible solution is the Data Pilot.
Back to top
View user's profile Send private message
JackieBaby
General User
General User


Joined: 02 Sep 2007
Posts: 11

PostPosted: Mon Sep 03, 2007 4:36 am    Post subject: Almost there! Reply with quote

Rick, I copied and pasted your formula, (thank you), and am trying to adjust it a little. I DOES add , or sum, all of column 'C' but it does not currently include column 'E'. I am still trying to 'read' the formula so in my own head I can figure out exactly why your formula works for column 'C' then I can adjust it to include column 'E', and then I can use it for the rest of the vendors. That little push over the edge was HUGE, thank you again! For several years I have wanted to be able to do this but never could, so I thought I always had to work strictly with numbers in the formulas. I'm really glad I asked!
Back to top
View user's profile Send private message
JackieBaby
General User
General User


Joined: 02 Sep 2007
Posts: 11

PostPosted: Mon Sep 03, 2007 4:43 am    Post subject: GOT IT! Reply with quote

Hey Rick,
OK, I changed the second SUMIF From 'C' to 'B' as in the first SUMIF
Quote:
=SUMIF(B1:B100;"Home Depot";C1:C100)+SUMIF(C1:C100;"Home Depot";E1:E100)
Now it reads =SUMIF(B1:B100;"Home Depot";C1:C100)+SUMIF(B1:B100;"Home Depot";E1:E100)

Thanks so much friend!
Back to top
View user's profile Send private message
RickRandom
Super User
Super User


Joined: 27 Jan 2006
Posts: 1082
Location: UK

PostPosted: Mon Sep 03, 2007 10:44 am    Post subject: Re: GOT IT! Reply with quote

JackieBaby wrote:
OK, I changed the second SUMIF From 'C' to 'B' as in the first SUMIF
My mistake, sorry, I was being a "muppet". Well done for working out the mistake.
Back to top
View user's profile Send private message
thomasjk
Super User
Super User


Joined: 16 Dec 2005
Posts: 2374

PostPosted: Mon Sep 03, 2007 12:18 pm    Post subject: Reply with quote

How about Data--->Subtotals? It does exactly what you want. I use each year to sum up the output from Quicken for all my tax deductible expenses.
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