Ask Your Question

Libre Calc If & And Statements (True/False)

asked 2018-05-08 13:47:06 +0200

Ding Duck gravatar image

updated 2018-05-08 14:37:44 +0200

Jim K gravatar image

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


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 anyone could explain, that would be appreciated.


edit retag flag offensive close merge delete



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


, and in failing case you have


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.

Mike Kaganski gravatar imageMike Kaganski ( 2018-05-08 13:57:42 +0200 )edit

Thanks Mike, I thought the Syntax was essentially the same, except I have nested another formulae in there <mid($b$9,6,2)&gt; anyway,="" i="" did="" discover="" a="" better="" way="" to="" achieve="" the="" result="" by="" using="" sumifs="" function,="" but="" do="" appreciate="" the="" reply="" thanks="" mike<="" p="">

Ding Duck gravatar imageDing Duck ( 2018-05-09 08:11:23 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2018-05-08 14:50:41 +0200

Jim K gravatar image

updated 2018-05-08 14:57:28 +0200

The corrected formula:

  • 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

edit flag offensive delete link more


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

Ding Duck gravatar imageDing Duck ( 2018-05-09 08:25:07 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-05-08 13:47:06 +0200

Seen: 13,452 times

Last updated: May 08 '18