Help with SharePoint Calculated Field

%3CLINGO-SUB%20id%3D%22lingo-sub-542740%22%20slang%3D%22en-US%22%3EHelp%20with%20SharePoint%20Calculated%20Field%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-542740%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20and%20thank%20you%20for%20your%20help.%3C%2FP%3E%3CP%3EI%20have%20a%20SharePoint%20List%20where%20I%20want%20a%20calculated%20field%20to%20be%20added%20where%20if%20%22product%22%20is%20A%20and%20%22platform%22%20is%201%2C%20then%20return%20the%20values%20%2211%2C%2012%2C%2013%22.%26nbsp%3B%20But%20I%20want%20to%20add%20multiples%20of%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%3A%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23000000%22%20face%3D%22Arial%20Narrow%22%20size%3D%222%22%3EIf%20product%26nbsp%3B%20line%20is%20%22A%22%20and%20platform%20is%20%221%22%20then%2011%2C%2012%2C%2013%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23000000%22%20face%3D%22Arial%20Narrow%22%20size%3D%222%22%3EIf%20product%20line%20is%20%22A%22%20and%20platform%20is%20%222%22%20then%2011%2C%2014%2C%2015%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23000000%22%20face%3D%22Arial%20Narrow%22%20size%3D%222%22%3EIf%20product%20line%26nbsp%3Bis%20%22A%22%20and%20platform%20is%20%223%22%20then%2016%2C%2011%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23000000%22%20face%3D%22Arial%20Narrow%22%20size%3D%222%22%3EIf%20product%20line%26nbsp%3Bis%20%22A%22%20and%20platform%20is%20%224%22%20then%201%2C%202%2C%203%2C%204%2C%205%2C%206%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23000000%22%20face%3D%22Arial%20Narrow%22%20size%3D%222%22%3EIf%20product%20line%20is%20%22A%22%20and%20platform%20is%20%225%22%20then%207%2C%208%2C%209%2C%2010%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20this%20formula%20but%20it%20didn't%20work...%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23000000%22%20face%3D%22Arial%20Narrow%22%20size%3D%222%22%3EIF(AND(%5BProduct%20Line%5D%3D%22A%22%2COR(%5BPlatform(s)%5D%3D%221%22)%2C11%2012%2013%2CIF(AND(%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23000000%22%20face%3D%22Arial%20Narrow%22%20size%3D%222%22%3E%5BPlatform(s)%5D%3D%222%22)%2C11%2014%2015%2Cif(and(%5BPlatform(s)%5D%3D%223%22)%2C16%2011%2CIF(AND(%5BPlatform(s)%5D%3D%224%E2%80%9D)%2C1%202%203%204%205%206%2CIF(AND(%5BPlatform(s)%5D%3D%225%22)%2C7%208%209%2010)))%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23000000%22%20face%3D%22Arial%20Narrow%22%20size%3D%222%22%3EAny%20help%20is%20greatly%20appreciated.%26nbsp%3B%20Thank%20you!%20%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-542740%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ELists%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-543692%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20SharePoint%20Calculated%20Field%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-543692%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F337278%22%20target%3D%22_blank%22%3E%40AmyS1996%3C%2FA%3E%26nbsp%3B%3CSPAN%3ESee%26nbsp%3BArticle%26nbsp%3B%3C%2FSPAN%3E%3CA%20href%3D%22http%3A%2F%2Fwww.viewmaster365.com%2Ffunctions%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%20target%3D%22_blank%22%3Ehttp%3A%2F%2Fwww.viewmaster365.com%2Ffunctions%3C%2FA%3E%3CSPAN%3E%26nbsp%3Bfor%20all%20SP%20functions%20and%20syntax.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3ESharePoint%20has%20the%20limitation%20of%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3Emaximum%207%20levels%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Edeep%20nested%20calls.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-543721%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20SharePoint%20Calculated%20Field%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-543721%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F337278%22%20target%3D%22_blank%22%3E%40AmyS1996%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20got%20this%20working%20for%20you%20I%20think%20per%20your%20requirements.%26nbsp%3B%20I%20created%203%20columns%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3EProduct%20Line%20(Choice)%3C%2FLI%3E%3CLI%3EPlatform%20(Choice)%3C%2FLI%3E%3CLI%3EReturn%20(Calculated)%3C%2FLI%3E%3C%2FUL%3E%3CP%3EI%20created%205%20list%20items%2C%20one%20to%20test%20each%20different%20Platform%20condition.%26nbsp%3B%20So%20for%20each%20item%20I%20chose%20a%20different%20platform%20then%20I%20created%20my%20formula.%26nbsp%3B%20Here%20it%20is%20working%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F112504i6AE67C981EE9CEC3%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22Annotation%202019-05-07%20161353.png%22%20title%3D%22Annotation%202019-05-07%20161353.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EIt%20can%20certainly%20confusing%20to%20work%20with%20these%20nasty%20nested%20formulas.%26nbsp%3B%20Here%20is%20the%20column%20formula%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3E%3DIF(AND(ProductLine%3D%22A%22%2CPlatform%3D%221%22)%2C%2211%2012%2013%22%2CIF(AND(ProductLine%3D%22A%22%2CPlatform%3D%222%22)%2C%2211%2014%2015%22%2CIF(AND(ProductLine%3D%22A%22%2CPlatform%3D%223%22)%2C%2216%2011%22%2CIF(AND(ProductLine%3D%22A%22%2CPlatform%3D%224%22)%2C%221%202%203%204%205%206%22%2CIF(AND(ProductLine%3D%22A%22%2CPlatform%3D%225%22)%2C%227%208%209%2010%22%2C%22not%20applicable%22)))))%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe%20do%20not%20need%20any%20OR%20statements%20as%20this%20is%20taken%20care%20of%20by%20the%20ELSE%20part%20of%20the%20IF%20statement.%26nbsp%3B%20You%20also%20weren't%20wrapping%20your%20return%20value%20numbers%20in%20quotes.%26nbsp%3B%20The%20way%20it%20works%20is%20using%20nested%20IF%20AND%20statements.%26nbsp%3B%20We%20say%20the%20first%20condition%20IF%20productline%3DA%20AND%20platform%3D1%2C%20then%20give%2011%2012%2013.%26nbsp%3B%20The%20key%20is%20the%20ELSE.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3EIF(AND(conditioncol1%3Dvalue1%2C%20conditioncol2%3Dvalue2)%2C%20%22returned%20value%22%2C%20ELSE)%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20start%20with%20the%20first%20statement%20then%20copy%20it%204%20times%20and%20fix%20the%20values%20for%20the%20other%204.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3EIF(AND(ProductLine%3D%22A%22%2CPlatform%3D%222%22)%2C%2211%2014%2015%22%2CELSE)%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3EIF(AND(ProductLine%3D%22A%22%2CPlatform%3D%223%22)%2C%2216%2011%22%2CELSE)%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3EIF(AND(ProductLine%3D%22A%22%2CPlatform%3D%224%22)%2C%221%202%203%204%205%206%22%2CELSE)%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3EIF(AND(ProductLine%3D%22A%22%2CPlatform%3D%225%22)%2C%227%208%209%2010%22%2C%22not%20applicable%22)%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20paste%20that%202nd%20IF%20statement%20in%20the%20ELSE%20of%20the%20previous%20one%20(e.g.%20paste%20platform%203%20formula%20in%20the%20ELSE%20of%20platform%202).%26nbsp%3B%20Then%20you%20keep%20doing%20that%20until%20you%20end%20up%20with%20one%20big%20fat%20long%20statement.%26nbsp%3B%20For%20me%2C%20it%20worked%20first%20time!%26nbsp%3B%20Let%20me%20know%20if%20I%20misunderstood%20if%20you%20needed%20something%20different.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1420793%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20SharePoint%20Calculated%20Field%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1420793%22%20slang%3D%22en-US%22%3EThank%20you!%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1420903%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20SharePoint%20Calculated%20Field%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1420903%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F5855%22%20target%3D%22_blank%22%3E%40Doug%20Allen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20this%20formula%20for%20a%20new%20project%2C%20not%20sure%20what%20is%20incorrect%2C%20as%20I%20am%20unfamiliar%20with%20formulas.%26nbsp%3B%20Is%20there%20a%20better%20way%20to%20write%20this%3F%20(2%20Product%20Lines%20and%20several%20regions%20and%20values)%20Thank%20you!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(AND(Product%20Line%3D%22A%22%2CRegion%3D%22GLOBAL%22)%2C%229%22%2CIF(AND(Product%20Line%3D%22A%22%2CRegion%3D%22AN%22%2C%22AM%22%2C%22CJ%22%2C%22EU%22%2C%22INDO%22)%2C%225%22%2CIF(AND(Product%20Line%3D%22A%22%2CRegion%3D%22ANZ%22%2C%22BRZ%22%2C%22CIS%22%2C%22LAB%22)%2C%223%22%2CIF(AND(Product%20Line%3D%22A%22%2CRegion%3D%22ASI%22%2C%22N1%22%2C%22N2%22)%2C%221%22%2CIF(AND(Product%20Line%3D%22B%22%2CRegion%3D%22AN%22%2C%22EU%22%2C%22N1%22)%2C%223%22%2CIF(AND(Product%20Line%3D%22B%22%2CRegion%3D%22AM%22%2C%22ANZ%22%2C%22ASI%22%2C%22BRZ%22%2C%22CIS%22%2C%22CJ%22%2C%22N2%22%2C%22IND%22%2C%22INDO%22%2C%22GLOBAL%22%2C%22LAB%22%2C%223%22%2C%22not%20applicable%22))))))%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hello and thank you for your help.

I have a SharePoint List where I want a calculated field to be added where if "product" is A and "platform" is 1, then return the values "11, 12, 13".  But I want to add multiples of this.

 

For example:

If product  line is "A" and platform is "1" then 11, 12, 13

If product line is "A" and platform is "2" then 11, 14, 15

If product line is "A" and platform is "3" then 16, 11

If product line is "A" and platform is "4" then 1, 2, 3, 4, 5, 6

If product line is "A" and platform is "5" then 7, 8, 9, 10

 

I tried this formula but it didn't work...

IF(AND([Product Line]="A",OR([Platform(s)]="1"),11 12 13,IF(AND(

[Platform(s)]="2"),11 14 15,if(and([Platform(s)]="3"),16 11,IF(AND([Platform(s)]="4”),1 2 3 4 5 6,IF(AND([Platform(s)]="5"),7 8 9 10)))

 

Any help is greatly appreciated.  Thank you!

 

4 Replies
Highlighted

@AmyS1996 See Article http://www.viewmaster365.com/functions for all SP functions and syntax. 

SharePoint has the limitation of maximum 7 levels deep nested calls.

Highlighted

@AmyS1996,

 

I got this working for you I think per your requirements.  I created 3 columns:

 

  • Product Line (Choice)
  • Platform (Choice)
  • Return (Calculated)

I created 5 list items, one to test each different Platform condition.  So for each item I chose a different platform then I created my formula.  Here it is working:

Annotation 2019-05-07 161353.png

It can certainly confusing to work with these nasty nested formulas.  Here is the column formula:

 

=IF(AND(ProductLine="A",Platform="1"),"11 12 13",IF(AND(ProductLine="A",Platform="2"),"11 14 15",IF(AND(ProductLine="A",Platform="3"),"16 11",IF(AND(ProductLine="A",Platform="4"),"1 2 3 4 5 6",IF(AND(ProductLine="A",Platform="5"),"7 8 9 10","not applicable")))))

 

We do not need any OR statements as this is taken care of by the ELSE part of the IF statement.  You also weren't wrapping your return value numbers in quotes.  The way it works is using nested IF AND statements.  We say the first condition IF productline=A AND platform=1, then give 11 12 13.  The key is the ELSE.  

 

IF(AND(conditioncol1=value1, conditioncol2=value2), "returned value", ELSE)

 

You start with the first statement then copy it 4 times and fix the values for the other 4. 

 

IF(AND(ProductLine="A",Platform="2"),"11 14 15",ELSE)
IF(AND(ProductLine="A",Platform="3"),"16 11",ELSE)
IF(AND(ProductLine="A",Platform="4"),"1 2 3 4 5 6",ELSE)
IF(AND(ProductLine="A",Platform="5"),"7 8 9 10","not applicable")

 

Then paste that 2nd IF statement in the ELSE of the previous one (e.g. paste platform 3 formula in the ELSE of platform 2).  Then you keep doing that until you end up with one big fat long statement.  For me, it worked first time!  Let me know if I misunderstood if you needed something different.  

Highlighted
Thank you!
Highlighted

@Doug Allen 

I tried this formula for a new project, not sure what is incorrect, as I am unfamiliar with formulas.  Is there a better way to write this? (2 Product Lines and several regions and values) Thank you!

 

=IF(AND(Product Line="A",Region="GLOBAL"),"9",IF(AND(Product Line="A",Region="AN","AM","CJ","EU","INDO"),"5",IF(AND(Product Line="A",Region="ANZ","BRZ","CIS","LAB"),"3",IF(AND(Product Line="A",Region="ASI","N1","N2"),"1",IF(AND(Product Line="B",Region="AN","EU","N1"),"3",IF(AND(Product Line="B",Region="AM","ANZ","ASI","BRZ","CIS","CJ","N2","IND","INDO","GLOBAL","LAB","3","not applicable"))))))