Create formula to count category risk with aggregate rating

Jorjie Higginson

Hi,

I have a risk register for a project that has well over 100 risks documented and I have them categorized into 17 different risks. I would like to be able to create a formula to count how many risks exist under each category and then a formula to show the aggregate risk associated with this. I would think by a weighting? 1 for low, 3 for medium, 9 for high?

I have extracted and mocked up (in excel to show what I want it to show) to be able to display on a dashboard.

Any help would be greatly appreciated :)

Jeff Reisman

This is very possible.

So the first thing you want to do is count all the Risk rows where the Risk Category equals the Category in this row of the chart. The handy thing about this formula is that you can use the same one in the Count column on each line of your chart.

=COUNTIF([Risk Category]:[Risk Category], Category@row)

This says to give us a count of every item in Risk Category (or whatever column you enter these categories into) where the value equals the Category listed on this row of the chart.

Next you want to assign the Aggregate Residual Risk. You will use an IF with a nested IF for this one.

=IF(Count@row < 3, "Low", (IF(Count@row < 9, "Medium", "High"))

This states that if the Count on that row is less than 3, enter "Low" into the line. If it's 3 or higher, consider this other IF function: If it's lower than 9, enter "Medium", otherwise enter "High". You can enter this same formula in each line of the chart as well.

Lastly, use conditional formatting to set the background color of the Aggregate Residual Risk column based on the value the formula puts there.

Jeff Reisman

For your Count column in your table, where 'Risk Category' is the name of the column containing the category of each risk:

=COUNTIF([Risk Category]:[Risk Category], Category@row)

This says count how many from the Risk Category column match the Category on this row of the chart.

For your Agg Residual Risk, use an IF followed by a nested IF:

=IF(Count@row < 3, "Low", (IF(Count@row < 9, "Medium", "High"))

This says If the count is less than 3, set the value to Low, otherwise use this other IF statement, which says if the count is greater than 3 but less than 9, set the value to Medium, but if it's 9 or higher, set the value to High.

Lastly, use conditional formatting to set he background color based on the value.

Jeff Reisman

If you want to use this in a metrics sheet, you'll want to reference the range on your main sheet. So if your main sheet is called "Risks," when you start entering your COUNTIF statement, click the link to reference another sheet.

Select your Risks sheet and then select the column that lists the risk category. You can name this range whatever you want, like "Risk Category Range." This establishes the reference range on your metrics sheet. So the formula would look something like this:

=COUNTIF({Risk Category Range}, Category@row)