IF statement to find a value

  • Hello again,

    Still working on that invoice template...

    Some of our customers are PST exempt, for those we have their PST number that appears on the invoice in cell B14.

    When making a "Supply Only" invoice we charge PST on all products for all customers that are not PST exempt.

    When making a "Supply & Install" invoice we don't charge PST on most of our products, however there are several items that we do charge the PST.

    On a Supply & Install job for a window package we charge PST on the window package but not the labour.

    On the invoice template I added a column (K) where the sales guys can mark either a "Y" to apply PST or a "N" for no PST on each line individually. (Hope that makes sense)

    Now I need a formula that will look at a couple thing to determine if PST should be added. I got part of the formula but can't seem to get the whole thing working right.

    First I need to see if there is a PST number for the customer, if there is then no PST gets applied on any items ever. *=IF(B14<=0,J17*0.08,"")*

    Second I need to see if there is a "Y" on any line, if there is then I need to multiply that line total by 8%. *=IF(K17="Y",J17*0.08,"")*

    J17 is the line total for one item, the Line totals are J17:J40.

    Those are the formulas I came up with, now I need to combine them and put the total PST for all lines in cell J45.

    I hope this makes sense :(

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!