Need help with nested IF/AND statement for a Calculated Column

[Jon Gertken]

I'm having issues getting a calculated column to play nice. Basically in a list I have 7 "Approval" columns that are populated via a Approval Flows. I'm wanting this calculated column to update to "Approved" once the other 7 columns all switch to "Approve". 

I either get a syntax message when I try to save the formula. Or the few iterations when it does accept it, it isn't behaving as intended. I'm sure I'm missing something obvious, but banging my head on the desk at this point. 

Please help!

=IF([Column1]=Approve,AND([Column2]=Approve,AND([Column3]=Approve,AND([Column4]=Approve,AND([Column5]=Approve,AND([Column6]=Approve,AND([Column7]=Approve, "Approved","Pending")))))))

[#2 · Accepted solution]

@Jon Gertken It's very easy to get tangled up in quotes and missing parens. Plus, you can only next about 7 IF statements, so that makes it more difficult.

What I've done in the past is rethink it a bit to make the syntax a bit easier. I'd count how many of the fields are 'Approved' and then do the logic.

That part would look like:

=IF([column1]="Approved",1,0)+IF([column2]="Approved",1,0)+IF([column3]="Approved",1,0)+IF([column4]="Approved",1,0)+IF([column5]="Approved",1,0)+IF([column6]="Approved",1,0)+IF([column7]="Approved",1,0)

If a column is 'Approved', it adds one to the total, if not, it adds 0. This works because you are using the + for adding.

Then, wrap that whole thing in a IF(<codeFromAbove>=7,"Approved","Pending"), so the full thing would look like:

=IF(IF([column1]="Approved",1,0)+IF([column2]="Approved",1,0)+IF([column3]="Approved",1,0)+IF([column4]="Approved",1,0)+IF([column5]="Approved",1,0)+IF([column6]="Approved",1,0)+IF([column7]="Approved",1,0)=7,"Approved","Pending")

[#3]

@Robin Nilsson 

Thanks a million, saved me some headache. Had to tweak it a little but works like a charm.