2008/7/25 Brian Barker <[EMAIL PROTECTED]>:
> 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.
Yes, in Dutch it's SOM.ALS, there's also AANTAL.ALS (NUMBERIF ?)
=SOM.ALS(A1:A10;"<0") sums negative values in the A1:A10 array
> 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".
indeed, nested SUM and IF, but I thought SUMIF could do the job (it seemed
to do it, with a single condition, as in the second formula I quoted)
>
> 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'll try it out at home, where I have the Calc file in question.
>
>
> 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) .
This might work, since there's a limited and known series of conditions
(sections of an organisation in the first array, types of income or expense
in the second - I put them into two columns because the second series occurs
in different sections)
>
>
> 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.
I'm sure it will, as usual.
Many thanks!
>
>
> Brian Barker
>
--
Guy
