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 '14 at 13:29
  • Thanks Paulster. I can only get one piece at a time working
    – Kms
    Feb 28 '14 at 19:00
  • say =IF(AND([Risk1]="Low",[Risk2]="Low",[Risk3]="Low",[Risk4]="Low",[Risk5]="Low",[Risk6]="Low"),"Low","Med")
    – Kms
    Feb 28 '14 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 '14 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 '14 at 19:19

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, privacy policy and cookie policy

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