At 13:18 20/07/2008 +0200, Guy Voets wrote:
I have a problem with 'conditional sum' (as it was called in Excel).
I believe it's SUM.IF in OOo. I'm spoiled by the way Excel led me
through the motions, and don't quite succeed in doing the same in OOo.
The idea is that when in the sheet 'journaal' two conditions are met
in a range of cells in columns C and D, the amounts in the
corresponding cells in column H will be summed in the result cell in
another (second) sheet This is how OOo translated the formula made
in the Excel sheet
=SOM(ALS($journaal.$C$2:$C$179="nau";ALS($journaal.$D$2:$D$179="zek";$journaal.$H$2:$H$179;0);0))
(SOM.ALS is dutch for SUM.IF)
I don't find an easy way to produce such formula in OOo. One
condition (C="nau") is OK with Insert>Function, but do I have to add
the second condition (D="zek") manually?
This is what I get:
=SOM.ALS(journaal.C2:C179;"sec";journaal.G2:G173)
First, a few observations:
o I'm puzzled by your reference to "SUM.IF" - with a dot. The
function is called "SUMIF" in the English version, not "SUM.IF". I
don't know about the Dutch implementation, of course.
o You say your problem is with SUMIF, but your example (translated
from Excel) does not use this function. Instead, it uses a nested
arrangements of the different - and separate - functions "SUM" and "IF".
o This example doesn't work as given. It is an array function, so
it needs to be entered with Ctrl+Shift+Enter instead of simple Enter,
whereupon it automatically grows braces: {=SUM( ... 0))}. Then it
works. But you probably knew that.
I don't think you can easily use the SUMIF function to achieve what
you need, in fact, since it is designed to examine a single array for
the condition, and you have two arrays and two conditions. The
reason you cannot add a second condition is that SUMIF does not need
or allow one. But you can create a formula like your example (using
SUM and IF):
o In the Function Wizard, select SUM and Next>>.
o Next to "number 1", press the "fx" button, and then select IF and Next>>.
o For Test, drag and type to create: $journaal.$C$2:$C$179="nau" .
o Before creating the Then_value, enter 0 for Otherwise_value. (You
can go back later if you forget this.)
o Next to Then_value, press the "fx" button, and then select IF and Next>>.
o For Test, drag and type to create: $journaal.$D$2:$D$179="zek" .
o For Then_value, drag to create: $journaal.$H$2:$H$179 .
o For Otherwise_value, enter: 0 .
o Crucially, tick the Array box at the bottom left.
o Press OK. Bingo!
(Perhaps you could do this, too.)
I think the only way to use SUMIF directly is if you first somehow
condense the two conditions into one. You could do this easily by
concatenating the test arrays in a new column, perhaps on your second
sheet. Put
=$journaal.$C$2&$journaal.$D$2
into A2, say, and then fill that down column A. Where both your
conditions are satisfied, this new cell will contain "nauzek". (You
can hide this column if you prefer.) Then you can replace your formula with
=SUMIF($A$2:$A$179;"nauzek";$journaal.$H$2:$H$179) .
Even that doesn't quite work, since the "nauzek" could have been
created by concatenating vales of "na" and "uzek" instead of "nau"
and "zek". To avoid this problem, interpolate an extra character,
e.g. a space, between the two values. Thus A2 becomes
=$journaal.$C$2&" "&$journaal.$D$2
and the test formula
=SUMIF($A$2:$A$179;"nau zek";$journaal.$H$2:$H$179) .
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
