IF help

  • Thread starter Thread starter louncov
  • Start date Start date
L

louncov

G'day,

I'm using the IF formula to caculate GST (tax) paid on items. I'm
using column D in sheets 'Codes' for non-GST items. Basically, if the
'code' equals 'rm' or 'nat', then GST is 0. In the main sheet, I've
previously hardcoded the formula for calculating GST as...

[Column D in the current sheet is the product code & column G is the
total cost in $]

=IF(OR('Codes'!D4="rm",'Codes'!D4="nat"),SUM(0),SUM(G4/11))

or instead of hardcoding...

=IF(OR(D3=Codes!D2,D3=Codes!D3),SUM(0),SUM(G3/11))

It works, but if I add another item in the 'Codes' sheet, I have to
rewrite the formulas again, which obviously, is not a good idea. I
tried creating a formula that would consider all items in 'Codes'
column D as non-GST...

=IF(D3='Codes'!D2:D100),SUM(0),SUM(G3/11))

This unfortunately, doesn't work. The syntax is correct, but the
result isn't. It only picks up one item (generally D2) from Column D
in 'Codes'.

Question 3a: What is the correct calculation?
Question 3b: Is there a way to check the whole column (D) in 'Codes'
sheet as apposed to a range of D2:D100? I'm not looking at expanding
that much (only have 10 non-GST items at the moment), but I certainly
don't want to revisit this problem in the future!

Again, thanks to all. Cov.
 
If I understand you

=IF(COUNTIF(codes!D:D,D3)>0,0,G3/11)

this should answer your questions

Lance
 
Back
Top