AverageIFs multiple columns when meets multiple criteria in one column

Question

I tried to look for answers online but I can't seem to find the answer :( I have multiple columns to average depending on multiple criteria in 1 column. Here's my sample data:

I used this formula but it only works when averaging 1 column (score 1)

=AVERAGE(IF((B3:B11={"Emp 1","Emp 2","Emp 3"}),C3:C11))

I tried to average Score 1 to Score 5 but it doesn't work. I'm getting "#N/A"

=AVERAGE(IF((B3:B11={"Emp 1","Emp 2","Emp 3"}),C3:G11))

Any help would be very much appreciated! Thank you! :)

Answer 1

Use this array formula:

=AVERAGE(IF((ISNUMBER(MATCH(B2:B10,{"Emp1","Emp2","Emp3"},0)))*(C2:G10<>""),C2:G10))

Being an array formula it must be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. If done correctly then excel will put {} around the formula.