Calculate the Total Amount based on Currency Selected from Drop Down List


=IF([Currency]22 = "AED", [Amount]22, IF([Currency]23 = "USD", [Currency]23 * [Rate]23, [Amount]24 * [Rate]24))

Best Answer

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @Denny V

    One approach is to place the rates in your summary field (right hand ribbon of your page). This will allow you to reference the rates and keep your formula formatted as a column formula. The Summary fields provide an easy way to store this type of referenced data within the sheet so that you don't have to hard-code the rate directly into the formula. If the list of conversion factors grow, we would consider a separate sheet that we could use as a lookup table.

    You will create a field for each rate in the Sheet summary section. You can call it whatever you like but I'll name them so you are clear on the formula.,

    The formula below is what goes in your TOTAL column

    =IF(Currency@row="AED", [AED Rate]#*Currency@row, IF(Currency@row="USD", [USD Rate]#*Currency@row, IF(Currency@row="EUR", [EUR Rate]#*Currency@row)))

    Would this work for you?

    Kelly

  • Thanks, Kelly for the response. however, it is showing INVALID OPERATIONS


  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey Denny

    Did you build the Sheet Summary fields?

  • Yes made the following ,



  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Just checking, the formula is working now, or do we still need to tweak it?

  • Dear Kelly ,

    The Formulae worked . Thank for your Help

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!