# Calculated formula to automate status of a project

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 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"))))
``````

• 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. 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 Sep 2, 2020 at 21:47

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")))
``````

• Good brother..What if there are null values..Is it possible to add isblank function to consider empty values 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. Sep 3, 2020 at 6:00

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"))))
``````