Trouble with IF statement results

[tfidler240]

I have the following IF statement:

if(SDUOM='FT' and SDUOM4='EA',SDUPRC/100000,if(substringcount('C,CF',SDUOM4)=1,SDUPRC/1000000,SDUPRC/10000))AS [Unit Price Calc]

I am trying to change the unit price to match the quantity UOM so the correct amount is use later to calculate dollar amount. 

If the quantity UOM (SDUOM) = FT and the pricing UOM (SDUOM4) = EA then the unit price is divided by 100000  - What works.

     If the pricing UOM (SDUOM4) is either C or CF then the unit price is divided by 1000000 - This doesn't work.

          Anything else the unit price is divided by 10000 - everything seems to be defaulting to this if it is not the first expression.

Before I added first expression - SDUOM='FT' and SDUOM4='EA' - both parts of the IF statement worked. 

Does anyone have a better way of multi-levle IF statements or what have I done wrong?

[Gysbert_Wassenaar · Accepted solution]

change: if(substringcount('C,CF',SDUOM4)=1,...

into:       if(match(SDUOM4,'C','CF'),...

[tfidler240]

That worked changing the substringcount function with a match function. 

Thanks.