Calculate the average across multiple columns and looking up a condition

Here is one more way to accomplish the desired output using one single dynamic array formula:

• Formula used in cell A6

=LET(
     a, A1:A4,
     b, TEXT(B1:J1,"mmmm"),
     c, UNIQUE(b,1),
     d, MAKEARRAY(ROWS(DROP(a,1)),COLUMNS(c),LAMBDA(x,y,
        AVERAGE(INDEX(FILTER(B2:J4,INDEX(c,,y)=b,0),x)))),
     HSTACK(a,VSTACK(c,d)))

If you have access to PIVOTBY() then :

=LET(
     a, TOCOL(IF(B2:J4<>"",A2:A4,NA()),2),
     b, TOCOL(IF(B2:J4<>"",B1:J1,NA()),2),
     c, TOCOL(IF(B2:J4<>"",B2:J4,NA()),2),
     PIVOTBY(a,TEXT(b,"m-mmmm"),c,AVERAGE,,0,,0))

Considering everything you stated, the solution should be very simple:

[L2 and fill to right then down as much as required]=AVERAGE(B2,E2,H2)

While the table structure is expected to be constant, you can fill down the formula (3 cells) for new projects.

Moreover, it can work automatically if you convert the range into the Excel table:

A couple more dynamic array variants for MS365 include...

GROUPBY:

=LET(
   grp, TRANSPOSE(GROUPBY(TOCOL(MONTH(B1:J1)), TRANSPOSE(B2:J4), AVERAGE,, 0)),
   HSTACK(A1:A4, VSTACK(TEXT(TAKE(grp, 1)*29, "mmmm"), DROP(grp, 1)))
)

REDUCE-BYROW:

=LET(
    hdr, TEXT(B1:J1, "mmmm"),
    unq, UNIQUE(hdr, 1),
    avg, REDUCE(A2:A4, unq, LAMBDA(a,v, HSTACK(a, BYROW(FILTER(B2:J4, hdr=v), AVERAGE)))),
    VSTACK(HSTACK("Project", unq), avg)
)

Note: if eta-lambdas are not yet available in your version of Excel, use LAMBDA(r, AVERAGE(r)) for the [function] argument of BYROW instead of just AVERAGE.

Here's one possible solution, but it requires Excel 365.

=BYROW(FILTER(INDEX($G$21:$O$23,XMATCH(F16,$F$21:$F$23),0),MONTH($G$20:$O$20)=1),LAMBDA(a,AVERAGE(a)))

With:

$G$21:$O$23 being your data set (values only);

F16 is your 'wanted' person/row;

$F$21:$F$23 being the list of persons;

$G$20:$O$20 is your row of dates, in date format (so not textstring);

MONTH($G$20:$O$20)=1 selects the prefered month, with 1 being January. If you like this is also customisable to be dynamic.

Clarified in the below image:

The formula presumes that the dates are real dates in the data table. Cell H28 and drag across

=AVERAGE(IF(TEXT($H$21:$P$21,"mmm")=H$27,INDEX($H$22:$P$24,MATCH($G28,$G$22:$G$24,0),0),""))

AVERAGEIF works only with cells, therefore the following formula uses AVERAGE function.
The rows can be modified, but when you expand the data table two arguments must be adapted in the INDEX function.

1. $H$22:$P$24 to $H$22:$P$xx
2. $G$22:$G$24 to $G$22:$G$xx
   where xx is the last row of the table.
   If you apply a table for the data range, then the formula can be modified to use the table's column names and then no changes are necessary since the table automatically expand the referenced columns in a table.