0

Can someone please help me make the needed correction for this IF statement to be used in sharepoint calculated column. I keep getting "The forumula contains a syntax error or is not supported" message. This was built in excel and now I've created a list in excel and trying to reuse the formula in sharepoint.

=IF(OR(ISBLANK([Product Risk]),ISBLANK([Supplier Risk]),ISBLANK([Business Priority]),ISBLANK([Brand Risk]),SUM([Product Risk],[Supplier Risk],[Business Priority],[Brand Risk])<4),"Incomplete risk fields",IF([GQA Category]="food",IF(OR(AND([Supplier Risk]=3,[Product Risk]=3),SUM([Product Risk],[Supplier Risk],[Business Priority],[Brand Risk])=12),"Level I",IF(OR(SUM([Product Risk],[Supplier Risk],[Business Priority],[Brand Risk])=10,SUM([Product Risk],[Supplier Risk],[Business Priority],[Brand Risk])=11),"Level II",IF(OR(SUM([Product Risk],[Supplier Risk],[Business Priority],[Brand Risk])=8,SUM([Product Risk],[Supplier Risk],[Business Priority],[Brand Risk])=9),"Level III",IF(AND(SUM([Product Risk],[Supplier Risk],[Business Priority],[Brand Risk])<8,SUM([Product Risk],[Supplier Risk],[Business Priority],[Brand Risk])>0),"Level IV","")))),IF([GQA Category]="non-food",IF(OR(SUM([Product Risk],[Supplier Risk],[Business Priority],[Brand Risk])>8,COUNTIF([Product Risk],[Supplier Risk],[Business Priority],[Brand Risk],3)>1),"High",IF(SUM([Product Risk],[Supplier Risk],[Business Priority],[Brand Risk])<6,"Low",IF(AND(SUM([Product Risk],[Supplier Risk],[Business Priority],[Brand Risk])>4,SUM([Product Risk],[Supplier Risk],[Business Priority],[Brand Risk])<9),"Medium"))))))

3

The nested Countif function is not valid. Countif does not exist in SharePoint. Although many SharePoint functions are identical to Excel functions, not all Excel functions work in SharePoint.

In Excel, the syntax is Countif(range, criterion). Used in Excel, the formula will error out because you use too many arguments for Countif.

In SharePoint the concept of a range as a set of cells does not exist. Also, in Excel, Countif() can be used on whole columns. In SharePoint, formulas cannot use a whole list column as a reference.

On another note, this formula is very long and convoluted. It has over 1200 characters and even with valid syntax might breach SharePoint limits. You may want to revise your data architecture and split the calculation over several columns.

| improve this answer | |
  • 1
    Indeed you need to replace COUNTIF with some other formula. See ViewMaster365.com/functions for a complete list and syntax. 1200 characters is no problem, 4000 is the maximum. You might also be helped with the CalcMaster Bookmarklet I wrote, it checks your Formula while editting in de SP edit screen. – Danny '365CSI' Engelman Jun 30 '15 at 10:01
  • @DannyEngelman, thanks for posting the limit. I knew there had to be one, just wasn't aware what it actually is. – teylyn Jun 30 '15 at 10:15
1

I always have trouble finding out if I should use comas or semi-colons as separators in calculated columns formulas. Try to change comas into semi-colons.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.