Calculated Column, Need help with formula please

[cocoloco79]

Hi everyone,

I'm trying the formula below, but if crop is not "sweet corn" the formula should return Prod[Crop.Units] but instead it returns blank cells. It does calcualte correctly when the IF condition is filled. Can someone help with this?

FME = IF(Prod[Crop] ="Sweet Corn",IF( and (Prod[OuterProduct] ="PROC - WWFME - Bean Carrot Corn - Woolworths FME 8 x 350g - Cartons",Prod[Boxes]*Prod[qtyinner]),Prod[Boxes x qtyinner],Prod[Crop.Units]))

[DallasBaba · Accepted solution]

@cocoloco79 Can you try

FME =
IF(
    Prod[Crop] = "Sweet Corn",
    IF(
        AND(
            Prod[OuterProduct] = "PROC - WWFME - Bean Carrot Corn - Woolworths FME 8 x 350g - Cartons",
            Prod[Boxes] * Prod[qtyinner]
        ),
        Prod[Boxes x qtyinner],
        Prod[Crop.Units]
    ),
    Prod[Crop.Units]
)

OR

FME = IF(
    Prod[Crop] = "Sweet Corn",
    IF(
        AND(
            Prod[OuterProduct] = "PROC - WWFME - Bean Carrot Corn - Woolworths FME 8 x 350g - Cartons",
            Prod[Boxes] * Prod[qtyinner]
        ),
        Prod[Boxes x qtyinner],
        IFBLANK(Prod[Crop.Units], 0)
    )
)

Let me know if this works for you. @ me in replies, or I'll lose your thread!!!  
If this post helps, please consider Accepting it as the solution to help others find it more quickly. 

Best Regards,

Dallas.

[cocoloco79]

@DallasBaba Legend, your first solution worked!!! Thanks a lot!