Forum Discussion

Chandrakanth K's avatar
Chandrakanth K
Copper Contributor
Aug 31, 2018

Excluding multiple values in a condition

Hello All,

 

In my below formula I want to exclude two values from Z2 Column. I,e.. Z999 And Z000.

I am able to add condition to exclude for Z999 but finding it difficult to exclude Z000 from same column. Need help

=(IF((A2="SAP"),IF((J2<>"Non PO Invoice"),(IF((Z2<>"Z999")*(COUNTIF(H:H,H2)=1)*(Y2<>"CB Retention / On Hold")*LEN(TRIM(Z2))*LEN(TRIM(AK2))*(TRIM(Z2)<>TRIM(AK2)),"Update","")),(IF((A2="SAP"),(IF((Z2<>"Z999")*(COUNTIF(H:H,H2)=1)*(Y2<>"CB Retention / On Hold")*LEN(TRIM(Z2))*LEN(TRIM(AA2))*(TRIM(Z2)<>TRIM(AA2)),"Update","")))))))

Regards,

Chandrakanth.K

 

excel
Formulas and Functions

1 Reply

  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    Aug 31, 2018

    Hi,

     

    Please explain what you are trying to do and what logic do you want to test?

     

    Anyway, please try to update the formula by using https://support.office.com/en-us/article/AND-function-5F19B2E8-E1DF-4408-897A-CE285A19E9D9 as follows:

     

    =(IF((A2="SAP"),IF((J2<>"Non PO Invoice"),(IF((AND(Z2<>"Z999",Z2<>"Z000"))*(COUNTIF(H:H,H2)=1)*(Y2<>"CB Retention / On Hold")*LEN(TRIM(Z2))*LEN(TRIM(AK2))*(TRIM(Z2)<>TRIM(AK2)),"Update","")),(IF((A2="SAP"),(IF((Z2<>"Z999")*(COUNTIF(H:H,H2)=1)*(Y2<>"CB Retention / On Hold")*LEN(TRIM(Z2))*LEN(TRIM(AA2))*(TRIM(Z2)<>TRIM(AA2)),"Update","")))))))