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.

olops
  • 21
  • 1
  • 2

3 Answers3

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

Matiur Rahman
  • 2,142
  • 2
  • 6
  • 8
  • 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 02 '20 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? – Matiur Rahman Sep 02 '20 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 02 '20 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

Matiur Rahman
  • 2,142
  • 2
  • 6
  • 8
  • Good brother..What if there are null values..Is it possible to add isblank function to consider empty values – olops Sep 02 '20 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 https://sharepoint.stackexchange.com/questions/19076/how-to-add-blank-to-choice-field – Matiur Rahman Sep 03 '20 at 01: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 03 '20 at 06: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"))))
Matiur Rahman
  • 2,142
  • 2
  • 6
  • 8