SOLVED

Calculated column from 7 Yes/No

%3CLINGO-SUB%20id%3D%22lingo-sub-868360%22%20slang%3D%22fr-FR%22%3ECalculated%20column%20from%207%20Yes%2FNo%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-868360%22%20slang%3D%22fr-FR%22%3E%3CP%3EHi%20everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20recently%20tried%20to%20use%20the%20columns%20calculated%20from%20SharePoint%20(first%20time%20trying)%20and%20I%20can't%20pull%20off%20what%20I%20need.%20Basically%2C%20I%20have%207%20Yes%2FNo%20controls%20and%20I%20simply%20want%20to%20create%20a%20column%20that%20return%20a%20string%20like%20%22Ok%22%20if%20all%20seven%20are%20%22Yes%22%20and%20%22Not%20Ok%22%20if%20not.%20I%20can%20make%20it%20work%20with%20one%20but%20I%20tried%20every%20IF%2FAND%20coding%20that%20I%20knew%20and%20I%20always%20displays%20the%20same%20thing%3A%20every%20column%20displays%20%22Not%20Ok%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20someone%20enlighten%20me%20please%20%3AD%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-868360%22%20slang%3D%22fr-FR%22%3E%3CLINGO-LABEL%3ESharePoint%20online%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-869008%22%20slang%3D%22en-US%22%3ERe%3A%20Calculated%20column%20from%207%20Yes%2FNo%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-869008%22%20slang%3D%22en-US%22%3EShould%20just%20be%20a%20bunch%20of%20IF's.%20Like%20so.%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3DIF(%5BColumn1%5D%3DYes%2CIF(%5BColumn2%5D%3DYes%2CIF(%5BColumn3%5D%3DYes%2CIF(%5BColumn4%5D%3DYes%2CIF(%5BColumn5%5D%3DYes%2CIF(%5BColumn6%5D%3DYes%2CIF(%5BColumn7%5D%3DYes%2C%20%22OK%22))))))%2C%20%22Not%20OK%22)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-869832%22%20slang%3D%22fr-FR%22%3ERe%3A%20Calculated%20column%20from%207%20Yes%2FNo%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-869832%22%20slang%3D%22fr-FR%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F869%22%20target%3D%22_blank%22%3E%40Chris%20Webb%3C%2FA%3E%20I%20tried%20it%2C%20doesn't%20seem%20to%20work.%20It's%20too%20bad%20SP%20doesn't%20show%20you%20where%20it%20comes%20from%20like%20in%20PowerApps%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-870227%22%20slang%3D%22en-US%22%3ERe%3A%20Calculated%20column%20from%207%20Yes%2FNo%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-870227%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F412778%22%20target%3D%22_blank%22%3E%40Devildez_1994%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20assume%20you've%20tried%20building%20this%20out%20one%20field%20at%20a%20time%20rather%20than%20all%207%20at%20once%2C%20right%3F%20Do%20one%2C%20make%20sure%20that%20works.%20Add%20another.%20Make%20sure%20that%20works%2C%20etc.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%2C%20note%20that%20in%20SharePoint%2C%20while%20the%20Yes%2FNo%20column%20reads%20text%2C%20the%20values%20stored%20are%20actually%20numbers.%20So%20when%20you%20do%20the%20calc%2C%20make%20sure%20you%20use%20numbers.%20I%20think%20Yes%20%3D1%20and%20No%20%3D%200.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-870337%22%20slang%3D%22en-US%22%3ERe%3A%20Calculated%20column%20from%207%20Yes%2FNo%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-870337%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F412778%22%20target%3D%22_blank%22%3E%40Devildez_1994%3C%2FA%3E%26nbsp%3B%20Use%20the%20%2B%20operator%20to%20add%20up%20each%20column%2C%20and%20if%20the%20total%20is%207%20then%20set%20your%20text.%20Here%20is%20a%20post%20I%20made%20last%20week%20where%20someone%20had%20the%20same%20issue%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FSharePoint%2FNeed-help-with-nested-IF-AND-statement-for-a-Calculated-Column%2Fm-p%2F861306%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FSharePoint%2FNeed-help-with-nested-IF-AND-statement-for-a-Calculated-Column%2Fm-p%2F861306%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-870401%22%20slang%3D%22en-US%22%3ERe%3A%20Calculated%20column%20from%207%20Yes%2FNo%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-870401%22%20slang%3D%22en-US%22%3EExcellent%20idea!%20I%20never%20thought%20of%20using%20simple%20addition.%20I%20like%20it!%3C%2FLINGO-BODY%3E
New Contributor

Hi everybody,

 

I recently tried to use the calculated columns from SharePoint (first time trying) and I can't pull off what I need. Basically, I have 7 Yes/No controls and I simply want to create a column that return a string like "Ok" if all seven are "Yes" and "Not Ok" if not. I can make it work with one but I tried every IF/AND coding that I knew and I always displays the same thing : every column displays "Not Ok"

 

Can someone enlighten me please :D ?

 

5 Replies
Should just be a bunch of IF's. Like so.

=IF([Column1]=Yes,IF([Column2]=Yes,IF([Column3]=Yes,IF([Column4]=Yes,IF([Column5]=Yes,IF([Column6]=Yes,IF([Column7]=Yes, "OK")))))), "Not OK")

@Chris WebbI tried it, does not seem to work. It's too bad SP does not show you where it comes from like in PowerApps^^

@Devildez_1994 

I assume you've tried building this out one field at a time rather than all 7 at once, right? Do one, make sure that works. Add another. Make sure that works, etc.

 

Also, note that in SharePoint, while the Yes/No column reads text, the values stored are actually numbers. So when you do the calc, make sure you use numbers. I think Yes =1 and No = 0. 

Best Response confirmed by Devildez_1994 (New Contributor)
Solution

@Devildez_1994  Use the + operator to add up each column, and if the total is 7 then set your text. Here is a post I made last week where someone had the same issue:

https://techcommunity.microsoft.com/t5/SharePoint/Need-help-with-nested-IF-AND-statement-for-a-Calcu...

 

Excellent idea! I never thought of using simple addition. I like it!