Libre Calc If & And Statements (True/False)

[DingDuck]

Could anyone shed some light on the following.
My Table is as below.

Payment  Join Date   Mthly Fees  Annual Fees  Jan    Feb
Monthly  2017/01/25  $10.00      $120.00      1      FALSE
Annual   2016/02/20  $20.00      $240.00      FALSE  FALSE
Monthly  2017/02/22  $30.00      $360.00      FALSE  TRUE

=IF(AND($A$9="Monthly",MID($B$9,6,2)="01",$C$9,$D$9))

Have checked the formatting of each Cell and set these to either Text or Date. In the result under ‘Jan’ (as shown by the value of 1) has been set to Boolean whereas all others are set to Text.

I am trying to extract the Value (in $) for each month. If a member is Monthly payment, then the value in Jan would be $10, Feb = $240 (Annual) etc.

If I use specific values as below, it works

Value 1  Value 2  Value 3  Value 4  Result
Yes      Monthly  10       120      10
Yes      Annual   20       240      240

=IF(AND(A3="Yes",B3="Monthly"),C3,D3)

If anyone could explain, that would be appreciated.

Cheers.

[mikekaganski]

You could want to check why in working case, you have brackets like

=IF(AND(<COND1>,<COND2>),<THEN>,<ELSE>)

, and in failing case you have

=IF(AND(<COND1>,<COND2>,<THEN-AS-COND3>,<ELSE-AS-COND4>)).

And don’t forget to change cells’ format back to numeric from boolean which was automatically applied when you used formulas that returned logical values.

[DingDuck]

Thanks Mike, I thought the Syntax was essentially the same, except I have nested another formulae in there <mid($B$9,6,2)>
Anyway, I did discover a better way to achieve the result by using SUMIFS Function, but do appreciate the reply
Thanks Mike

[jimk]

The corrected formula:

=IF(AND($A9="Monthly";MID($B9;7;2)="01");$C9;$D9)

• As @Mike_Kaganski hinted, the parentheses were misplaced in the first formula.
• It looks like 6 should be 7 in the MID statement.
• These ranges should be $A9 rather than $A$9 so that the formulas adjust by row when filled.

Example file: Monthly or Annual Payment.ods

[DingDuck]

Thanks Jan, I see your point about the parentheses. Sharp eyes!
I used 6 in the Mid statement so I could capture months 10,11,12
The $A9 was only a little test that I had done, but see your point.
As replied to Mike I used the following to get a better solution from where I was originally heading.
=SUMIFS($AW$2:$AW$3022,$AP$2:$AP$3022,$AP$2,$M$2:$M$3022,“01”)+SUMIFS($AW$2:$AW$3022,$AP$2:$AP$3022,$AP$7)
Just have to change the “01” to “02” for Feb etc. Cheers