1

Running SP19 On Prem. Have a list with a column looking at type category with three options, and a completed column. I am looking at a calculated field/column in the list to filter a view with.

  • 1st condition- Type A and yes
  • 2nd condition Type B and Yes
  • 3rd condition- Type C and complete doesn't matter.

What I have so far is:

=IF(AND([Validation Type]="Technical and Business Validation,[Technical Validation Complete]="Yes","Yes",IF([Validation Type]="Business Validation Only","Yes",IF([Validation Type]="Technical Validation Only","No")))

The filters seem to work with condition 2 and 3; but, when I add in the first statement, syntax errors. Have been trying to work this out several different ways and no success.

All help is appreciated.

3
  • What is the data type of [Technical Validation Complete] column? Choice or Yes/No?? Jul 29 at 14:24
  • Also, what should be the outcome/result of 2nd & 3rd conditions? "Yes" or "No"? Jul 29 at 14:26
  • The data type for complete is choice. The second should be yes and the third no.
    – JS009
    Jul 29 at 14:54
0

If [Technical Validation Complete] column is of type "Choice", use this:

=IF(AND([Validation Type] = "Technical and Business Validation", [Technical Validation Complete] = "Yes"), "Yes", IF(AND([Validation Type] = "Business Validation Only", [Technical Validation Complete] = "Yes"), "Yes", IF([Validation Type] = "Technical Validation Only", "No", "")))

If [Technical Validation Complete] column is of type "Yes/No", use this:

=IF(AND([Validation Type] = "Technical and Business Validation", [Technical Validation Complete]), "Yes", IF(AND([Validation Type] = "Business Validation Only", [Technical Validation Complete]), "Yes", IF([Validation Type] = "Technical Validation Only", "No", "")))

Official Documentations:

  1. Calculated Field Formulas.
  2. IF function.
2
  • 1
    Ganesh you are awesome! This saved me a ton of mental stress and fixed my problem. Thank you so very much and for also not making me feel like a dummy for the question. Appreciate the help.
    – JS009
    Jul 29 at 14:56
  • Great, glad it worked for you! Jul 30 at 3:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.