I have a SharePoint 2010 Consensus column that weights the values of six potential risk raters. My column names are [risk1], [risk2], [risk3], [risk4], [risk5], [risk6]. Any risk rater who rates an item has a choice of Low, Med, or High. Not all risk raters will rate every item and some items will have no rating initially.

My consensus logic is:

If all risk raters = Low then [Consensus] = Low;

If any risk rater (who rates) = Med and not High then [Consensus] = Med;

If any risk rater = High then [Consensus] = High

I can't get the formula right, any help would be greatly appreciated.


  • What do you have so far? Feb 28, 2014 at 13:29
  • Thanks Paulster. I can only get one piece at a time working
    – Kms
    Feb 28, 2014 at 19:00
  • say =IF(AND([Risk1]="Low",[Risk2]="Low",[Risk3]="Low",[Risk4]="Low",[Risk5]="Low",[Risk6]="Low"),"Low","Med")
    – Kms
    Feb 28, 2014 at 19:00
  • I tried addng a COUNTA condition to the above so that null values would not be evaluated but I am getting an illegal error.
    – Kms
    Feb 28, 2014 at 19:02
  • I also tried several combinations of the following just to see if I could combine two if statements =IF(AND([Risk1]="Low",[Risk2]="Low",[Risk3]="Low",[Risk4]="Low",[Risk5]="Low",[Risk6]="Low"), "Low", "Med"),IF(OR([Risk1]="High",[Risk2]="High",[Risk3]="High",[Risk4]="High",[Risk5]="High",[Risk6]="High"), "High", "Med") Thanks!
    – Kms
    Feb 28, 2014 at 19:19

1 Answer 1


This is what I came up with:


Hope it helps someone else!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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