How to Conditionally Require Data in more than one SharePoint Column

Question

I am attempting to calculate the outcome a status field based on the outcome of other columns. They are all Test Fields.

For Example:

"Overall Approval Status" column is to be based on Accepted/Rejected outcome in 8 Columns "Approver Status 1, "Approver Status 2", "Approver Status 3", "Approver Status 4", "Approver Status 5","Approver Status 6","Approver Status 7","Approver Status 8".

If any of the Approver Status fields has outcome of "Rejected" then the Overall Approver Status Column will return "Rejected". If all Approver Status columns have an outcome of "Approved" then the Overall Approver Status Column will return"Approved"

how would I write this formula for the " Overall Approver Status" Column?

I am really stumped and could you some expert assistance to solve this issue.

Answer 1

It's preferred to use a workflow to achieve nested if easily using SharePoint Designer as the following:

Otherwise, you will need to write multiple lines in the Calculated column to achieve the nested If!

To use nested-If in a calculated column, check

• SharePoint calculated column nested IF statement tutorial
• Calculated Column with Nested IF statments

Answer 2

This returns "Approved" if all eight are "Approved", otherwise "Rejected".

=IF(AND([Approver Status 1]="Approved",[Approver Status 2]="Approved",[Approver Status 3]="Approved",[Approver Status 4]="Approved",[Approver Status 5]="Approved",[Approver Status 6]="Approved",[Approver Status 7]="Approved",[Approver Status 8]="Approved"),"Approved","Rejected")

This is a bit better as it returns "Rejected" if any are rejected ("OR") or "Approved" if all are approved ("AND"), otherwise returns "Pending".

=IF(AND([Approver Status 1]="Approved",[Approver Status 2]="Approved",[Approver Status 3]="Approved",[Approver Status 4]="Approved",[Approver Status 5]="Approved",[Approver Status 6]="Approved",[Approver Status 7]="Approved",[Approver Status 8]="Approved"),"Approved",IF(OR([Approver Status 1]="Rejected",[Approver Status 2]="Rejected",[Approver Status 3]="Rejected",[Approver Status 4]="Rejected",[Approver Status 5]="Rejected",[Approver Status 6]="Rejected",[Approver Status 7]="Rejected",[Approver Status 8]="Rejected"),"Rejected","Pending")

Little better formatted version:

=IF(
  AND([Approver Status 1]="Approved",
      [Approver Status 2]="Approved",
      [Approver Status 3]="Approved",
      [Approver Status 4]="Approved",
      [Approver Status 5]="Approved",
      [Approver Status 6]="Approved",
      [Approver Status 7]="Approved",
      [Approver Status 8]="Approved"),"Approved",

  IF(OR([Approver Status 1]="Rejected",
        [Approver Status 2]="Rejected",
        [Approver Status 3]="Rejected",
        [Approver Status 4]="Rejected",
        [Approver Status 5]="Rejected",
        [Approver Status 6]="Rejected",
        [Approver Status 7]="Rejected",
        [Approver Status 8]="Rejected"),"Rejected",

   "Pending")