James, Take a look at this sample that I set up based on your example. You will see one tab where I have put all of your income and expense transactions, a second tab that contains the criteria for the DSUM formulas, and the leading tab which allows you to input your selection (in this case the month you want to see) and see the results (I called it the report tab). Also note that I like to use range names a lot, so look at my named ranges - they are pretty self explanatory.
I know this is an extremely simple model, but you can expand on it. I am an accountant by trade and used this DSUM formula extensively in the past to do just the sort of thing you are talking about, I also use the SUMIF function and it has it's place. What I do not like about either of these choices is that if a new "account" shows up, you will have to build another criteria. If this your case, you should also consider a database ... but that is another discussion and not the question you asked. Hope this is of some use to you. You should really play around with DSUM. You will like it. Best Regards Steve On 11/30/07, James Elliott - WA Rural Computers <[EMAIL PROTECTED]> wrote: > > Thanks for that information, Steven > > I have used Brian Barker's advice to solve my problem. However, I didn't > know about DSUM and the other "database" commands, so thank you for > pointing > me at them. I will go back and have a read and play with them and see > where > they might fit in to my needs. > > Many thanks, James > > ----- Original Message ----- > From: "Steven Hunlow" <[EMAIL PROTECTED]> > To: <[email protected] > > Sent: Wednesday, November 28, 2007 5:59 PM > Subject: Re: [users] CALC FORMULA QUESTION.? > > > > James, > > > > I think you would have better luck with the DSUM formula vs the SUMIF > > layout. Unfortunately, at this very moment I do not have time to walk > you > > through it, but I think it will give you greater flexibility in using a > > large list of criteria. I have never tried it using multiple sheets - > > thanks for the idea. You seem very knowledgable and experienced so work > > > with the DSUM formula and see if it doesn't answer your need. > > > > Steve Hunlow > > > > > > On 11/25/07, James Elliott - WA Rural Computers <[EMAIL PROTECTED] > > > > wrote: > >> > >> I have a spreadsheet cashbook that I designed and implemented in OOo > Calc > >> ver 2.3 running under Windows XP. > >> > >> I list all my transactions in columns like this: > >> code date description amount > >> v 5/6/07 vehicle expenses $59.70 > >> i 10/8/07 income $95.00 > >> > >> On another sheet within the same workbook I summaries of all the money > >> spent > >> or earned in the differing categories, for my accountant. For example, > >> my > >> formula to calculate the sum of all vehicle expenses is: > >> =SUMIF($NAB.$A$7:NAB.$A$2001; "v"; $NAB.$E$7:$NAB.$E$2001) > >> > >> and my formula for adding up my taxable income is: > >> > >> =SUMIF($NAB.$A$7:NAB.$A$2001; "i"; $NAB.$F$7:$NAB.$F$2001) > >> > >> where NAB is the name of the Sheet on which the transactions are listed > >> > >> What these formulas do, using the vehicle one as an example, is to add > up > >> all amounts coded with a "v". What I want to do now is to be able to > >> specify a date range so i can ask Calc to give me a total for vehicle > >> expenses incurred between the two dates. If I enter the Start Date in > >> D5, > >> and the End Date in D6, in pseudo-code, this is what I want: > >> > >> SUMIF( NAB.A7:NAB.A2001; "v" AND (TransactionDate=>StartDate) AND > >> (TransactionDate=<EndDate); sum-range) > >> > >> I think you can combine conditions using the * operator eg > >> (.........)*(...........) but I can't find my way back to that bit in > the > >> help files. > >> > >> > >> > >> I would be very much obliged if any of you could point me to an elegant > > >> solution to my problem. > >> > >> Many thanks, James > >> > >> > >> > >> --------------------------------------------------------------------- > >> To unsubscribe, e-mail: [EMAIL PROTECTED] > >> For additional commands, e-mail: [EMAIL PROTECTED] > >> > >> > > > > > > -------------------------------------------------------------------------------- > > > No virus found in this incoming message. > Checked by AVG Free Edition. > Version: 7.5.503 / Virus Database: 269.16.10/1160 - Release Date: > 29/11/2007 > 8:32 PM > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > >
SampleDSUMformula.ods
Description: application/vnd.oasis.opendocument.spreadsheet
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
