Hi Harold
I now have Brian's answer - thanks
When you said I was unsubscribed I resubscribed and re-posted my question.
In fact, I have been subscribed since OOo 1.0 was first released, but I have
changed from dial-up to a satellite Internet system and may have used a
different e-mail address - certaily a different IP route. So, Please ignore
my request to re-send Brian's answer.
Many thanks, James
----- Original Message -----
From: "Harold Fuchs" <[EMAIL PROTECTED]>
To: <[email protected]>; <[EMAIL PROTECTED]>
Sent: Tuesday, November 27, 2007 9:39 AM
Subject: Re: [users] Array Formulas
On 26/11/2007 18:20, Brian Barker wrote:
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]
Forwarding to unsubscribed OP
--
Harold Fuchs
London, England
Please reply *only* to [email protected]
---------------------------------------------------------------------
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.8/1153 - Release Date: 26/11/2007 9:08 PM
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
