7 Replies Latest reply: Oct 8, 2015 3:07 AM by Louise Grauss-Spoormaker RSS

    If, then and else formula with a sum in it

    Louise Grauss-Spoormaker

      How do I write the right formula for:

       

      If [Field B] =0

      then

      sum([Field C]*[Field D])

       

      If [Field B] >=1

      then

      sum([Field B]*[Field C]*[Field D])

       

      I want to make an Master Item of it for a KPI diagram and I want the total sum in it.

      In the example that would be € 4.312,60

       

      Field AField BField CField DSum
      81229,952.875,20
      02039,95799,00
      4819,95638,40
      Sum4.312,60

       

      I want it to be looked at per line so I think that the 'sum' comes first and then the 'if' but I can not get a working formula.

       

      I tried it both ways

       

      sum(if( [Field B] = 0, Then [Field C]*[Field D], Else[Field B]*[Field C]*[Field D]))


      and


      if [Field B] = 0,

      Then sum([Field C]*[Field D]),

      Else sum([Field B]*[Field C]*[Field D]))

      End if


      and many versions of it, with and without commas or with or without spaces or with or without '= sign', or with and without enters, with capital letters, etc.


      Is there anyone who can help me?