SOLVED

Sharepoint Calculated Column

%3CLINGO-SUB%20id%3D%22lingo-sub-2323289%22%20slang%3D%22en-US%22%3ESharepoint%20Calculated%20Column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2323289%22%20slang%3D%22en-US%22%3E%3CP%3EI've%20been%20trying%20to%20figure%20out%20an%20IF%20formula%20for%20a%20Sharepoint%20Calculated%20Column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EColumn%20A%20%3D%20IA%2C%20IB%2C%20IC%2C%20ID%2C%26nbsp%3BIIA%2C%20IIB%2C%20IIC%2C%20IID%2C%26nbsp%3BIIIA%2C%20IIIB%2C%20IIIC%2C%20IIID%2C%26nbsp%3BIVA%2C%20IVB%2C%20IVC%2C%20IVD%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20a%20calculated%20column%20where%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIF%20Column%20A%20%3D%20IA%2C%20IB%2C%20IIA%2C%20then%20calculated%20column%20%3D%201%3C%2FP%3E%3CP%3EIF%20Column%20A%20%3D%20IC%2C%20IIB%2C%20IIIA%2C%20then%20calculated%20column%20%3D%202%3C%2FP%3E%3CP%3EIF%20Column%20A%20%3D%20ID%2C%20IIC%2C%20IIIB%2C%20IVA%2C%20then%20calculated%20column%20%3D%203%3C%2FP%3E%3CP%3EIF%20Column%20A%20%3D%20IID%2C%20IIIC%2C%20IVB%2C%20then%20calculated%20column%20%3D%204%3C%2FP%3E%3CP%3EIF%20Column%20A%20%3D%20IIID%2C%20IVC%2C%20then%20calculated%20column%20%3D%205%3C%2FP%3E%3CP%3EIF%20Column%20A%20%3D%20IVD%2C%20then%20calculated%20column%20%3D%206%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20ideas%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2323289%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ELists%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESharePoint%20Online%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2324797%22%20slang%3D%22en-US%22%3ERe%3A%20Sharepoint%20Calculated%20Column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2324797%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1045023%22%20target%3D%22_blank%22%3E%40petertheo89%3C%2FA%3E%26nbsp%3BTry%20using%20below%20formula%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-javascript%22%3E%3CCODE%3E%3DIF(OR(%5BColumn%20A%5D%3D%22IA%22%2C%20%5BColumn%20A%5D%3D%22IB%22%2C%20%5BColumn%20A%5D%3D%22IIA%22)%2C%201%2C%20IF(OR(%5BColumn%20A%5D%3D%22IC%22%2C%20%5BColumn%20A%5D%3D%22IIB%22%2C%20%5BColumn%20A%5D%3D%22IIIA%22)%2C%202%2C%20IF(OR(%5BColumn%20A%5D%3D%22ID%22%2C%20%5BColumn%20A%5D%3D%22IIC%22%2C%20%5BColumn%20A%5D%3D%22IIIB%22%2C%20%5BColumn%20A%5D%3D%22IVA%22)%2C%203%2C%20IF(OR(%5BColumn%20A%5D%3D%22IID%22%2C%20%5BColumn%20A%5D%3D%22IIIC%22%2C%20%5BColumn%20A%5D%3D%22IVB%22)%2C%204%2C%20IF(OR(%5BColumn%20A%5D%3D%22IIID%22%2C%20%5BColumn%20A%5D%3D%22IVC%22)%2C%205%2C%20IF(%5BColumn%20A%5D%3D%22IVD%22%2C%206%2C%200))))))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EReturn%20the%20calculated%20field%20as%20%3CSTRONG%3ENumber%3C%2FSTRONG%3E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ENote%3C%2FSTRONG%3E%3A%3CBR%20%2F%3ESometimes%20comma(%2C)%20does%20not%20work%20in%20formula%20(I%20am%20not%20sure%20but%20it%20is%20based%20on%20something%20language%20or%20regional%20settings%20on%20your%20site).%20So%20in%20that%20case%20use%20semicolon(%3B)%20instead%20of%20comma(%2C).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EOfficial%20Documentations%3C%2FSTRONG%3E%3A%3C%2FP%3E%3COL%3E%3CLI%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fprevious-versions%2Foffice%2Fdeveloper%2Fsharepoint-2010%2Fbb862071(v%253Doffice.14)%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3ECalculated%20Field%20Formulas%3C%2FA%3E.%3C%2FLI%3E%3CLI%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Fif-function-7025be14-5665-43d0-af20-8293d1fe9d3a%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EIF%20function%3C%2FA%3E.%3C%2FLI%3E%3C%2FOL%3E%3CHR%20%2F%3E%3CP%3EPlease%20click%26nbsp%3B%3CSTRONG%3EMark%20as%20Best%20Response%3C%2FSTRONG%3E%26nbsp%3B%26amp%3B%20%3CSTRONG%3ELike%3C%2FSTRONG%3E%20if%20my%20post%20helped%20you%20to%20solve%20your%20issue.%20This%20will%20help%20others%20to%20find%20the%20correct%20solution%20easily.%20It%20also%20closes%20the%20item.%20If%20the%20post%20was%20useful%20in%20other%20ways%2C%20please%20consider%20giving%20it%20%3CSTRONG%3ELike%3C%2FSTRONG%3E.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2340604%22%20slang%3D%22en-US%22%3ERe%3A%20Sharepoint%20Calculated%20Column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2340604%22%20slang%3D%22en-US%22%3EWorks%20great%2C%20thanks!%3C%2FLINGO-BODY%3E
New Contributor

I've been trying to figure out an IF formula for a Sharepoint Calculated Column.

 

Column A = IA, IB, IC, ID, IIA, IIB, IIC, IID, IIIA, IIIB, IIIC, IIID, IVA, IVB, IVC, IVD

 

I want a calculated column where:

 

IF Column A = IA, IB, IIA, then calculated column = 1

IF Column A = IC, IIB, IIIA, then calculated column = 2

IF Column A = ID, IIC, IIIB, IVA, then calculated column = 3

IF Column A = IID, IIIC, IVB, then calculated column = 4

IF Column A = IIID, IVC, then calculated column = 5

IF Column A = IVD, then calculated column = 6

 

Any ideas

2 Replies
best response confirmed by petertheo89 (New Contributor)
Solution

@petertheo89 Try using below formula:

 

=IF(OR([Column A]="IA", [Column A]="IB", [Column A]="IIA"), 1, IF(OR([Column A]="IC", [Column A]="IIB", [Column A]="IIIA"), 2, IF(OR([Column A]="ID", [Column A]="IIC", [Column A]="IIIB", [Column A]="IVA"), 3, IF(OR([Column A]="IID", [Column A]="IIIC", [Column A]="IVB"), 4, IF(OR([Column A]="IIID", [Column A]="IVC"), 5, IF([Column A]="IVD", 6, 0))))))

 

Return the calculated field as Number.

 

Note:
Sometimes comma(,) does not work in formula (I am not sure but it is based on something language or regional settings on your site). So in that case use semicolon(;) instead of comma(,).

 

Official Documentations:

  1. Calculated Field Formulas.
  2. IF function.

Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

Works great, thanks!