multiple VAT % sum

hello all

I think this simple but I cant get it right for sum reason

I have 4 columns VAT % , NET Price and VAT, plus Inv total

basically VAT % has a drop down of different vat charged 6 options

I want my formula to look at option picked in column 1 (VAT % ) and the amount in Column 2 Net price to tell me in column 3 ( VAT ) what the vat should be .

my 1st part works

but when trying extend the formula

it will still work for 1st part but not for 2nd , it brings back 0.00 it should be bringing back 5 % ..

I need it to do calculation based 1st column 6 different percentages.


if it see's, OZR: outgoing VAT - Tax Free bring back oo.oo

or if , ONT: outgoing VAT - 17.5% bring back 17.5%

or if , OR5: outgoing VAT - 5% bring back 5 %

or if , OST: outgoing VAT - 20% bring back 20% and so on



  • Ray Lindstrom
    Ray Lindstrom ✭✭✭✭✭✭
    edited 03/02/23


    You only provided 4 of the 6 VAT % values. Here's the formula with those 4. Feel free to modify to add the other 2, or let me know what they are, and I'll modify it for you. So far here's what I have. Plug it in, and see if it works! It worked on my testing.

    =IF([VAT %]@row = "OZR: outgoing VAT - Tax Free", [Net Price]@row * 0, IF([VAT %]@row = "ONT: outgoing VAT - 17.5%", [Net Price]@row * 0.175, IF([VAT %]@row = "OR5: outgoing VAT - 5%", [Net Price]@row * 0.05, IF([VAT %]@row = "OST: outgoing VAT - 20%", [Net Price]@row * 0.2, ""))))

    All the best,


    MATAYLOR ✭✭✭

    Thank you so much Ray

    I thought that was the idea I could only get two to work once changed it abit yesterday, I must of been miss typing something yours is spot on Thank you so much

  • Ray Lindstrom
    Ray Lindstrom ✭✭✭✭✭✭


    Happy to help, and so glad it worked!



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!