At 16:43 26/11/2007 +0900, James Elliott wrote:
I have created a cashbook using Calc (ver 2.3) and it is working
fine. The column headers are:
code date description amount
If I want to get a total for all of my vehicle expenses, I can use
this formula:
=SUMIF(NAB.A7:NAB.A2001; "v"; NAB.E7:NAB.E2001)
where:
NAB is the name of the sheet
A7:A2001 the range
"v" is the code I use for vehicle expenses
What I want to do now, for my accountant, is to sum all the
expenditure in a particular category, between two given dates, so,
if I have put the Start Date in cell G1, and the End Date in G2, in
pseudocode this is what I want to achieve:
=SUMIF((A7:A2001 = "V") AND (G1<B7:B2001<G2); sum E7:E2001)
I want to add up all expenses that match a particular code and
occurred between specified dates.
I am sure I can use an array formula (registered using
Ctrl+Shift+Enter) but I cannot seem to get one to work and I would
appreciate it if you could point me in the right direction.
Tempting though SUMIF is, I don't see a simple way of using this to
do what you need. But I think there is an easy way out. It relies
on the fact that you can (mis)interpret logical values as numbers, in
which case TRUE maps to 1 and FALSE maps to 0. So you can switch on
or off numerical values, as it were, by multiplying them by the
appropriate logical values. You have three conditions here - the "v"
code and each of the date limits - and you want all of them to be
true before an amount is included in the total. So you just need to
multiply your amounts by all three conditions before summing them.
Try this:
{=SUM(E7:E2001*(A7:A2001="v")*(B7:B2001>=G1)*(B7:B2001<=G2))}
As you say, the braces are not typed but appear when you press
Ctrl+Shift+Enter. (I've omitted your sheet name for simplicity.)
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
