# average metric using multiple criteria in one range

✭✭✭✭

I have a sheet with risk scores for various projects. A project may have multiple risks in the sheet, with a project number that relates to another sheet.

I want to create a metric that will show the average risk score for "project 1", "project 3", "project 4", "project 7" to use a simplistic example.

I have been experimenting with some suggestions I have seen for average formulas but nothing for this specific example of multiple criteria from one range.

Could someone help with this?

• ✭✭✭✭✭✭

Are you wanting to average those four projects separately or get a combined average?

• ✭✭✭✭

ideally i would like to be able to do both, but in the first place I am looking for a combined average

• ✭✭✭✭✭✭

Try something like this...

=AVG(COLLECT({Score Column}, {Project Name Column}, OR(@cell = "Project 1", @cell = "Project 2", @cell = "Project 3", @cell = "Project 4")))

For just one project...

=AVG(COLLECT({Score Column}, {Project Name Column}, @cell = "Project 1"))

• ✭✭✭✭

many thanks, this looks like some of what i have seen, but seems like it would solve my separating out the different projects. i wil let you know how it goes