SOLVED

COUNTIF calculated column formula for Sharepoint Online

Copper Contributor

Hello, I have a formula that I got to work in excel, but am having trouble translating into a SharePoint

Online calculated column formula.

 

Am I able to use COUNT IF in a nested formula like this? I have 12 columns. I don't want the column to calculate a score until all questions are answered. Further, I want it to produce a fail if they get an "inaccurate" in any one of 5 specific columns or produce a fail there is an "inaccurate" in two or more of 7 specific columns.

 

What do I have wrong?  

 

=IF(OR(ISBLANK([Q1?]),ISBLANK([Q2?]),ISBLANK([Q3?]),ISBLANK([Q4?]),ISBLANK([Q5?]),ISBLANK([Q6?]),ISBLANK([Q7?]),ISBLANK([Q8?]),ISBLANK([Q9]),ISBLANK([Q10?]),ISBLANK([Q11?])),"",IF(OR([Q1?]="Inaccurate",[Q2?]="Inaccurate",[Q4?]="Inaccurate",[Q5]="Inaccurate",[Q10?]="Inaccurate"),"Fail",IF(COUNTIF([Q3?]="Inaccurate",[Q6?]="Inaccurate",[Q7]="Inaccurate",[Q8?]="Inaccurate",[Q9?]="Inaccurate",[Q11?]="Inaccurate",[Q12?]="Inaccurate")>2,"Fail","Pass"))

3 Replies

@CristinaMorales COUNTIF is not supported in SharePoint calculated formulas.


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.

For SharePoint/Power Platform blogs, visit: Ganesh Sanap Blogs

Thanks @ganeshsanap. What if I created another calculated column, set to number, to get a Count of "inaccurates", and then adjusted my formula so it looks at that Count column to see if the number is greater than two? Would something like the below work?

=IF(OR(ISBLANK([Q1?]),ISBLANK([Q2?]),ISBLANK([Q3?]),ISBLANK([Q4?]),ISBLANK([Q5?]),ISBLANK([Q6?]),ISBLANK([Q7?]),ISBLANK([Q8?]),ISBLANK([Q9]),ISBLANK([Q10?]),ISBLANK([Q11?])),"",IF(OR([Q1?]="Inaccurate",[Q2?]="Inaccurate",[Q4?]="Inaccurate",[Q5]="Inaccurate",[Q10?]="Inaccurate"),"Fail",IF[FailPassPW-LR-Count]>2,"Fail","Pass"))
best response confirmed by CristinaMorales (Copper Contributor)
Solution

@CristinaMorales Yes, workaround by creating another calculated column and using it will work. Or you have to make changes to existing formula which will become complicated. So, try using another calculated column.


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.

For SharePoint/Power Platform blogs, visit: Ganesh Sanap Blogs

1 best response

Accepted Solutions
best response confirmed by CristinaMorales (Copper Contributor)
Solution

@CristinaMorales Yes, workaround by creating another calculated column and using it will work. Or you have to make changes to existing formula which will become complicated. So, try using another calculated column.


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.

For SharePoint/Power Platform blogs, visit: Ganesh Sanap Blogs

View solution in original post