0

Please i need help.I have 3 columns namely

Child care status, Education status, Micro business status.

Each of the columns above have (Completed, Initiated, Approved, Partially completed) as their drop down options

I intend to create a calculated field that returns

Completed (If all of the 3 columns reads completed)

Ongoing (If any of the 3 columns reads Either "Initiated or Approved)

**partially completed ** (If any of the 3 columns reads completed)

Please i crave your indulgence to please share your ideas as to how i can get this done.

3 Answers 3

0

Please see the formula below. What would it read if all three read "Partially Completed"? I've set it as "Initiated". Also, assuming that these are required fields, so didn't check for empty values.

=IF(AND(AND(CareStatus="Completed",EducationStatus="Completed"),BusinessStatus="Completed"),"Completed",IF(OR(OR(CareStatus="Completed",EducationStatus="Completed"),BusinessStatus="Completed"),"Partially Completed",IF(OR(OR(CareStatus="Initiated",EducationStatus="Initiated"),BusinessStatus="Initiated"),"Ongoing",IF(OR(OR(CareStatus="Approved",EducationStatus="Approved"),BusinessStatus="Approved"),"Ongoing","Initiated"))))

enter image description here

3
  • Hi Matiur,I appreciate your support. If any of the 3 columns reads partially completed, The final status should read partially completed.You are a star. Awaiting your feedback.
    – olops
    Sep 2, 2020 at 17:49
  • This contradicts your logic with "Ongoing" final status. For example, 1 column has "Partially Completed", the remaining two have "Approved", then what would be the final status? "Partially Completed" or "Ongoing"; can't be both? Sep 2, 2020 at 19:51
  • I must have gotten you confused with my initial question...Let me reiterate. Please pardon me..Let it be like this. Completed (If any of the 3 columns reads "completed") Initiated (If any of the 3 columns reads "Initiated") partially completed (If any of the 3 columns reads "Partially completed) Approved (If any of the any columns reads "Approved") Please consider ISBLANK for null values. Thanks Hero
    – olops
    Sep 2, 2020 at 21:47
0

See the revised formula and screenshot below.

=IF(AND(AND(CareStatus="Completed",EducationStatus="Completed"),BusinessStatus="Completed"),"Completed",IF(OR(OR(OR(CareStatus="Completed",EducationStatus="Completed"),BusinessStatus="Completed"),OR(OR(CareStatus="Partially Completed",EducationStatus="Partially Completed"),BusinessStatus="Partially Completed")),"Partially Completed",IF(OR(OR(OR(CareStatus="Initiated",EducationStatus="Initiated"),BusinessStatus="Initiated"),OR(OR(CareStatus="Approved",EducationStatus="Approved"),BusinessStatus="Approved")),"Ongoing","Initiated")))

enter image description here

3
  • Good brother..What if there are null values..Is it possible to add isblank function to consider empty values
    – olops
    Sep 2, 2020 at 22:22
  • In fact, for Choice field, rendered as dropdown list, SharePoint doesn't allow a blank value. There are not-recommended solutions to it though. See here sharepoint.stackexchange.com/questions/19076/… Sep 3, 2020 at 1:06
  • Hi Matiur, I used Microsoft info path for the front end forms..So it allows you to choose a blank field.so its possible for only one columns to have an input while the 2 others remain blank.Thanks for your help..Let me try the formula and give you a feed back.
    – olops
    Sep 3, 2020 at 6:00
0

Added ISBLANK() to the formula - if any of the 3 columns is blank, the final status is "Initiated" or you can replace whatever value you deem appropriate.

As I've mentioned in my comment earlier, SharePoint Choice column (dropdown list) doesn't allow a Blank or Empty value. I've added to the formula in case you use text column for your data entry.

=IF(OR(OR(ISBLANK(CareStatus),ISBLANK(EducationStatus)),ISBLANK(BusinessStatus)),"Initited",IF(AND(AND(CareStatus="Completed",EducationStatus="Completed"),BusinessStatus="Completed"),"Completed",IF(OR(OR(OR(CareStatus="Completed",EducationStatus="Completed"),BusinessStatus="Completed"),OR(OR(CareStatus="Partially Completed",EducationStatus="Partially Completed"),BusinessStatus="Partially Completed")),"Partially Completed",IF(OR(OR(OR(CareStatus="Initiated",EducationStatus="Initiated"),BusinessStatus="Initiated"),OR(OR(CareStatus="Approved",EducationStatus="Approved"),BusinessStatus="Approved")),"Ongoing","Initiated"))))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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