Maximum number of IF statements in a Calculated Column?

If you said seven, or eight, you spoke too fast…

I just answered a question on the Stump the Panel forum, and thought others could be interested.

The context

I recently published a new version of my tool for color coding calendars and lists. This tool generates a formula that associates each item of a choice list with a color:


For example, if the [Status] column contains Choice5, the output of the formula will be DarkCyan.

The issue

In practice, users often have more than 7 items in their choice list. For 8 choices, the formula becomes:


But if you try it, here is the message SharePoint throws back at you:
The formula contains a syntax error or is not supported.

So here is the sad truth: you cannot have more than seven nested IF statements in a calculated column.

When users discover this – by trial and error, via a forum, or from their experience with Excel – the next step for them is usually to try and break their formula across multiple columns, each containing a maximum of seven nested IFs. However, in most cases there is a simpler way.

The workaround

We have seen that a calculated column doesn’t allow more than seven nested IFs. But what if they are not nested? Let’s give it a try with my formula generator:
First pass: choice1 to choice7
Second pass: choice 8 to choice 14

Now, I just have to concatenate these two using the & symbol:


There is still something wrong as the “” (no selection) option appears twice. So I need to remove the redundant one (in orange above, don’t forget the last parenthesis) to get my final result:


And voila, a formula that fits in one calculated column, with 15 IF functions!

“That’s cool, Christophe! So… when can we see this in your formula generator?”
Ok, I’ll update it… but seriously, is it reasonable to use more than 7 colors for color coding?

Other options

For the record, IF is not the only SharePoint function allowing to build conditional formulas. See for example in this post how I used the CHOOSE function. And yes, there will be another formula generator built on CHOOSE!

73 thoughts on “Maximum number of IF statements in a Calculated Column?

  1. I agree – BRILLIANT!!! More colors are useful when you are using them to differentiate events by department or group instead of a degree of status. We have a combined calendar for several groups to list their events and people love to differentiate between the group events by color. “Oh a limegreen event! That’s me!” I guess each group knows their own color, so they are really only looking for that one color amongst the sea of many others. Make sense?

    • Right. I didn’t want to mention it now… because it will be in Larry’s article in a couple days.
      I have actually identified 3 limits:
      – formula length: 1024 characters
      – calculation length: 2048 characters
      – filter length: 256 characters

  2. Hi Christophe

    Perhaps 15 is way too many for colours, but at least 12 is required when using months of the year.

    I ran across this problem last year, when I had to stop a 7. Same in Excel which I got a work around for as well.

    Thanks for this formula.


  3. This made perfect sense to me and allowed me to input it into MOSS2007 calendar list fine…BUT when I add the 8th item choice it only displays the second set of colors.

    Im using your formula and using an “individual” field instead of “status” field. Im also using names for choices and standard colors to display. If I cut the code in half before the &IF statement, I see 8 colors. When I add the &IF and the 9th choice and color, Im only able to see the 9th choice color. If I add out to 14 choices, I only see colors for 9-14. Do you have any ideas as to why? Ive even copied and pasted straight from your website into MOSS and modified the column names but I cant seem to find the problem.


    Im stumped.

  4. Nevermind, I found it. I typed it right when I replied, but I forgot this , and thats what caused the problem.


  5. The nested IF conditionals here are EUREKA – and not related to the color coding intiative. I have been in a place before when I needed to organize a list for display or reporting (think “supercategory” gathering of items by “category” – and ran into the 7 conditional limit. Thanks for the illumination of a way to bust out beyond that!!!!

  6. So…

    =IF([Event Type]=”AAAHC”,”#990000″,IF([Event Type]=”ASEV”,”#000066″,IF([Event Type]=”ATSEP”,”#996600″,IF([Event Type]=”COMSEC”,”#339900″,IF([Event Type]=”ESOHCAMP”,”#000099″,IF([Event Type]=”HSI”,”#CC6600″,IF([Event Type]=”LCAT”,”#996600″)))))))&IF([Event Type]=”ORI”,”#FFFFFF”,IF([Event Type]=”PME”,”#660099″,IF([Event Type]=”SEPWO”,”#000000″,IF([Event Type]=”UCI”,”#FFFFFF”))))

    results in …

    COMSEC = #339900FALSE
    AAAHC = #990000FALSE
    LCAT = #996600FALSE
    ASEV = #000066
    …and the ones below the “&”

    So I obvisouly want to get ride of the “FALSE”, what am I missing?

    • In the first part of your expression, before the ampersand, you need to return nothing (“”) if no condition is met.
      Something like this:

      IF([Event Type]="LCAT","#996600","")))))))&...

      Same in the second part, after the ampersand.

  7. I’m trying to use this technique to set a Status text column automatically when certain milestone date columns have a value. My formula is below. It works perfectly if none of the conditions before the & are met, but when one of the conditions before the & is true, it displays the text for that condition and the text for the first IF statement following the &.

    =IF([Production Release Date]>0,”In Production”,
    IF([Code Delivered]>0,”Client Testing”,
    IF([BA Testing Completed]>0,”Ready for Delivery”,
    IF([BA Testing Started]>0,”BA Testing”,
    IF([Developer Testing Started]>0,”Developer Testing”,
    IF([Development (Coding) Started]>0,”In Development”,
    IF([Functional Spec Approved]>0,”Approved for Coding”,””)))))))
    &IF([Functional Spec Complete]>0,”Functional Spec Review”,
    IF([Functional Spec Started]>0,”Functional Spec Started”,
    IF([BRD Received]>0,”Requirements Review”,
    IF([Start Date]>0,”Awaiting Approved Requirements”))))

    For example, If [Start Date] through [Functional Spec Started] dates have values, “Funtional Spec Review” is displayed (which is correct). But if [Start Date] through [Developer Testing Started] have values, “Developer TestingFunctional Spec Review” is displayed.

    Is there a way around this? Thanks for your help!

  8. Pingback: Tutorial: add color coding to your SharePoint 2007 calendar in 15 minutes « Path to SharePoint

  9. My code is below….I’m still getting “6FALSE” and “5FALSE” when page is refreshed. What am I doing wrong?

    =IF([Rank / Title]=”Gen”,1,IF([Rank / Title]=”Lt Gen”,2,IF([Rank / Title]=”Maj Gen”,3,IF([Rank / Title]=”Brig Gen”,4,IF([Rank / Title]=”GS-15″,4.1,IF([Rank / Title]=”Col”,5,IF([Rank / Title]=”GS-14″,5.1,IF([Rank / Title]=”Lt Col”,6,””))))))))&IF([Rank / Title]=”CMSgt”,6.1,IF([Rank / Title]=”GS-13″,6.2,IF([Rank / Title]=”Maj”,7,IF([Rank / Title]=”SMSgt”,7.1,IF([Rank / Title]=”GS-12″,7.2,IF([Rank / Title]=”MSgt”,8,IF([Rank / Title]=”Capt”=8.1,IF([Rank / Title]=”GS-11″,8.2,””))))))))

  10. Pingback: b r e n d a n n e w e l l . c o m » Colour coded SharePoint 2010 Calendar

    • That is indeed Brilliant!

      However I have a situation where I have more than 15 items I need to loop. Not for colors , but I have a list of names and titles. Using ‘&’ works once…so is there a work around for that as well?? I know…greedy me!!

      • Rinse and repeat! The same tip will apply to 22 statements, or 29, etc.

        If you reach the maximum number of characters allowed in one formula, then you’ll need to use more than one calculated column.

  11. Hi, great workaround. Following on from the last comment, is it true you can only use ‘&’ once with this workaround? I have 19 conditions but only want to display results in 1 calculated column. Thanks

  12. This is EXCELLENT. Am a new SP2007 Site Collection Admin and found your tutorial on adding 7 colors to calendars. Can you tell me where to find the one which allows more colors?

  13. Hi Guys,
    I have to solve my nested issue. These are 3 If’s that I need to concatenate. I’m tring to do it but without any success. Please is there sonmeone able to help me please.

    >>>> Linea1 =IF(AND(Importo>50000,Importo35%),”Orange”,IF(AND(Importo>100000,Importo35%),”Red”,IF(AND(Importo>1000000,StandardTerms=”Yes”,RiskFactors=”No”,GM>35%),”Blu”,”NO”)))
    >>>> Linea2 =IF(AND(Importo>25000,Importo25%,GM100000,Importo25%,GM500000,StandardTerms=”Yes”,RiskFactors=”No”,GM>25%,GM>>> Linea3 =IF(AND(Importo>25000,Importo<100000,StandardTerms="Yes",RiskFactors="No",GM500000,StandardTerms=”Yes”,RiskFactors=”No”,GM<25%),"Blu","NO"))

  14. Pingback: 如何向 sharepoint2007 日历中添加颜色 | CS Magazine

  15. The work around is great. I had to calculate the month numbers from the month name so I used your solution but changed it up some:

    Worked perfectly.

  16. I’ve got this to work fine on a Rank system and it assigned the values perfectly (21 different rank values with 3 nested ‘&’ statements). However, when I try to sort on the value of this column it doesn’t work as expected, they are still all over the place rather than sequentially… any ideas?

  17. Hi
    I successfully implemented your tool for 7 choices. However, I cannot seem to get this to work for our 10 choices. I even re-created your scenario exactly (created a Status Column with 14 Choices) and then copied and pasted your code as is, to check whether my changes for our scenario caused the problem. In both cases I get the error below. What am I doing wrong?
    The formula contains a syntax error or is not supported. at Microsoft.SharePoint.Library.SPRequestInternalClass.UpdateField(String bstrUrl, String bstrListName, String bstrXML)
    at Microsoft.SharePoint.Library.SPRequest.UpdateField(String bstrUrl, String bstrListName, String bstrXML)

  18. What am i doing wrong – below is the code I am using.

    =IF([LikeLihood x Impact]=””,””,IF([LikeLihood x Impact]=”1×1″,”Low”,IF([LikeLihood x Impact]=”1×2″,”Low”,IF([LikeLihood x Impact]=”1×3″,”Low”,IF([LikeLihood x Impact]=”1×4″,”Low”,IF([LikeLihood x Impact]=”1×5″,”Medium”,IF([LikeLihood x Impact]=”2×1″,”Low”,IF([LikeLihood x Impact]=”2×2″,”Low”,IF([LikeLihood x Impact]=”2×3″,”Low”,IF([LikeLihood x Impact]=”2×4″,”Medium”,IF([LikeLihood x Impact]=”2×5″,”Medium”,IF([LikeLihood x Impact]=”3×1″,”Low”,IF([LikeLihood x Impact]=”3×2″,”Medium”,IF([LikeLihood x Impact]=”3×3″,”Medium”,IF([LikeLihood x Impact]=”3×4″,”Medium”,IF([LikeLihood x Impact]=”3×5″,”High”))))))))))))))))&IF([LikeLihood x Impact]=””,””,IF([LikeLihood x Impact]=”4×1″,”Low”,IF([LikeLihood x Impact]=”4×2″,”Medium”,IF([LikeLihood x Impact]=”4×3″,”Medium”,IF([LikeLihood x Impact]=”4×4″,”High”,IF([LikeLihood x Impact]=”4×5″,”High”,IF([LikeLihood x Impact]=”5×1″,”Low”,IF([LikeLihood x Impact]=”5×2″,”Medium”,IF([LikeLihood x Impact]=”5×3″,”High”,IF([LikeLihood x Impact]=”5×4″,”High”,IF([LikeLihood x Impact]=”5×5″,”High”,)))))))))))

    • I have an issue that has me stumped – I’m trying to build an IF statement to extract a month value for one of my lists.

      Here’s my formula: =IF([Start Date]=””,”Missing A Date”,IF([Start Date]<="1/31/2012","01-JAN",IF([Start Date]<="2/28/2012","02-FEB",)))

      Problem is even with records that have a start date with FEB in them, still returns 01-JAN. If date record is blank – returns "Missing A Date". I found another way around to get desired result but I'm curious why it keeps returning 01-JAN. Need a push in proper direction. Thanks

  19. Christophe,
    Thanks, this has been very helpful.

    I had a requirement to colour code a Project Risk Register. The first 2 values were selected from lists, so the technique described here and in your other posts on lists worked fine. The 3rd value was a Risk Score calculated from the first 2 values, therefore the above formula wouldn’t work. Anyhow, after a little research & head scratching I came up with the following, which when integrated with the rest of your published technique allowed me to colour code a calculated value where the colour is determined by the range of the calculated value between 1 & 30. If its less than 5, then Yellow. If its between 5 & 14, then Orange. If its between 15 & 23, then Red. It its greater than 24 then Indigo.

    =”<span style='width:100%;display:inline-block;text-align:center;border:1px solid"
    &”;’> “&RSV&””

    Hope this is helpful for someone else

    • The code should be:

      =”<span style='width:100%;display:inline-block;text-align:center;border:1px solid"
      &”;’> “&RSV&””

  20. Thank you Christophe!
    I was able to tweak your formula to work for my project – I have one column with a drop-down list of the states, and needed to create a second column that calculated the EPA Region (1-10) based on the state selected:


    As you used in your example, ” ” with an ampersand at the end of each nested IF statement string also worked.

    Region map:

  21. Not bad Chris. keep up all the good work!

    NB: a perhaps simplier option (much easier to maintain) would be results of the following:

    IF( [Field]=”Option A”, “Color A”, “”)
    ,IF( [Field]=”Option B”, “Color B”, “”)
    ,IF( [Field]=”Option C”, “Color C”, “”)
    ,IF( [Field]=”Option n”, “Color “, “”)

    Much easier to edit afterwards.

  22. Okay Chris i need your help I saw in a previous post for the same question but the answer was not clear to me. I am following your instructions and but i get a “AgenciesFALSE” back. how do i fix this?

    here is my formula:

    =IF(LEFT([FP Number],2)=”WF”,”Agencies”,IF(LEFT([FP Number],2)=”WR”,”Funds”,IF(LEFT([FP Number],2)=”WU”,”Work Orders”,IF(LEFT([FP Number],2)=”WW”,”Reimbursable”,IF(LEFT([FP Number],2)=”WT”,”Agreement”,IF(LEFT([FP Number],2)=”WY”,”Services”,IF(LEFT([FP Number],2)=”MR”,”Technical”)))))))&IF(LEFT([FP Number],2)=”WK”,”Work”,IF(LEFT([FAP Number],2)=”WO”,”Others”))

    • Hi, can you give me any idea on how to host an infopath form on ofifce 365 with anonymous access? I know there is no support for this but any workaround or tricks available to make it done. thanks

  23. Here’s my issue. I have successfully embedded the nested IF’s. My issue resides in the logic of my formulas. If SharePoint find a Denominator value of ZERO or EMPTY it replaces it with a 0 (the reason of this I have to do a division, hence the search and replace). when I add the & to consider a particular set of values, the Formula ends up concatenating the original calculation (Numerator / Denominator) and the 0 I’m asking to put as a replacement. I need that in the end if uses on of the 2 possible values…Need Help. Here’s my latest attempt:
    =IF([Library Key]=”0454″,(1-(Numerator/Denominator)),IF([Library Key]=”0443″,(1-(Numerator/Denominator)),IF([Manual Calculation]””,[Manual Calculation],IF(Denominator=0,0,IF(Denominator=””,””,IF(Data_Type=”System Generated”,(Numerator/Denominator),[Manual Calculation]))))))&IF([Library Key]=”0142″,Numerator,IF([Library Key]=”0146″,Numerator,IF([Library Key]=”0147″,Numerator,IF([Library Key]=”0148″,Numerator,IF([Library Key]=”1793″,Numerator,IF([Library Key]=”3239″,Numerator,IF([Library Key]=”0053″,0,””)))))))

  24. Hi Christophe

    I know this works, but when I used it in this scenario, I get double entries.

    for example, DC Review is empty, it should jsut say With DC for Review, but I actually get With DC for ReviewDC PDF & HETrans to FC which is combination of the first item in the first nested statement along with the first item in the 32 nested statement.

    I can see why it is happening, but cannot figure out how to fix it.

    Any ideas would be appreciated.

    =IF([DC Review]=””,”With DC for Review”,
    IF([Drafting Complete]=””,”Being Drafted”,
    IF([Drafting Print Copy]=””,”Being Printed for Review”,
    IF([PE Review]=””,”With Calgary PE for Review”,
    IF([Sent to Site PE]=””,”With FC Site PE for Review”,
    IF([DC File Received]=””,”Still with FC Site PE”,
    IF([Drafting Update]=””,”Drafting Update following FC Site Review”,””)))))))
    &IF([DC PDF & HETrans to FC]=””,”DC PDF & HETrans to FC”,
    IF([DC Native & HETrans to CenDC]=””,”DC Native & HETrans to CenDC”,
    IF([DC Upload PDF]=””,”DC to Upload PDF”,
    IF([DC copy to Stick File]=””,”DC to File Drg in Stick File”,”Call if you get this message”))))


  25. Hi Christophe

    Very informative blog you have going on here! Thank you for contributing your expertise! I have a problem similarly described by your colors example. Mine is listed below and I would welcome your thoughts as I still get the syntax error message.




  26. Hi Christopher,

    Can you help me with this? I’m getting a syntax error. If I remove the end “” I then get the word FALSE attached to the column entry. Ex: HighFALSE or FALSELow.

    IF(AND([Likelihood]=”Low”,[Impact]=”Medium”),”Medium”, “”)))))&
    IF(AND([Likelihood]=”Low”,[Impact]=”High”),”Medium”, “”))))

    I tried scanning through all the above comments but must be missing something.

  27. Hi there, PLEASE HELP. Just cannot seem to get this right. Trying to return high, med or low statement based a column with numbers: my formula:
    Where am I going wrong, I keep getting a “yes” or “no”.

    Please also help with this one: I keep getting a certain answer:

    =IF([ProbabilityRating]>0,IF([ProbabilityRating]<50,"Unlikely",IF([ProbabilityRating]<75,"Likely",IF([ProbabilityRating]<=100,"Highly Likely","Certain",””))))

    Hope to hear from you soon. Is there anyway to check these formulas in SharePoint Designer

    • Hi Cindy

      In your first example, you have an extra (

      Try this:


      In your second question


      I am sure in every case it will be greater than 0, so you need to think about that as the formula is just following what you have told it to do. Alo note that for each of your statements you have stated the outcome such as Likely etc, but nothing for the first statement.

      This is something that you can setup and try in Excel before you put it into Sharepoint.

  28. I’m using this trick to next IF(AND statements where the result is a date. I don’t get any errors with my formula but the result in the column called “Target Due Date” is a number, not a date. I have the field listed as a date type field. When I only had 7 nested statements it worked fine. As soon as I added the & and the other 7 nested statements it ceased displaying as a date? I’m mystified as to how to resolve this.

      • I think when I concatenate it is overriding the date format of the field somehow and just giving me the date as a number. If anyone has a solution I’d appreciate it.

  29. Pingback: SharePoint List – Custom Progress Calculated Column | Valy Greavu's Live Blog

  30. Pingback: SharePoint List – Custom Progress Calculated Column

  31. Pingback: SharePoint Ordering for humans | Lamby and Friends

  32. Hi,

    In the Script below, if the select the percentage as 100 instead of the first IF condition (Perc>=75), the second IF condition is getting executed i.e the background color for the condition =”75%”,””&Percentage&””,IF(Percentage<="25%","”&Percentage&””,IF(AND(Percentage>”25%”,Percentage<="74%"),"”&Percentage&””)))

    Kindly help!!

  33. I maintain a Risk Log and I just created a calculated column that will render the ‘Overal Risk Ranking’ based on two columns (Risk Probability and Risk Impact). For Example: If a Risk is classified as being “Very High Probability”, but has a “Low Impact”, the Overall Risk Ranking = Low. I was unable to complete the IF Formula due to character limitation. Anyone have a suggestion to workaround this?

    =CONCATENATE(IF(AND([Risk Probability]=”VH”,[Risk Impact]=”VH”),”VH”,IF(AND([Risk Probability]=”VH”,[Risk Impact]=”H”),”H”,IF(AND([Risk Probability]=”VH”,[Risk Impact]=”M”),”H”,IF(AND([Risk Probability]=”VH”,[Risk Impact]=”M”),”H”,IF(AND([Risk Probability]=”VH”,[Risk Impact]=”L”),”L”,IF(AND([Risk Probability]=”H”,[Risk Impact]=”VH”),”H”,IF(AND([Risk Probability]=”H”,[Risk Impact]=”H”),”H”,IF(AND([Risk Probability]=”H”,[Risk Impact]=”M”),”M”,IF(AND([Risk Probability]=”H”,[Risk Impact]=”L”),”L”,IF(AND([Risk Probability]=”M”,[Risk Impact]=”VH”),”H”,IF(AND([Risk Probability]=”M”,[Risk Impact]=”H”),”M”,IF(AND([Risk Probability]=”M”,[Risk Impact]=”M”),”M”,IF(AND([Risk Probability]=”M”,[Risk Impact]=”L”),”L”,IF(AND([Risk Probability]=”L”,[Risk Impact]=”VH”),”H”,IF(AND([Risk Probability]=”L”,[Risk Impact]=”H”),”M”,IF(AND([Risk Probability]=”L”,[Risk Impact]=”M”),”L”,IF(AND([Risk Probability]=”L”,[Risk Impact]=”L”),”L”,IF(AND([Risk Probability]=”VL”,[Risk Impact]=”VH”),”M”,IF(AND([Risk Probability]=”VL”,[Risk Impact]=”H”),”L”,”None”))))))))))))))))))))

    • never mind, I used the magic “&” to link up the IFs and I abbreviated the words so I save the number of characters.

Comments are closed.