Sum values based on XLOOKUP

The table below shows a list of employees and number of hours they have worked in that week. I am trying to come up with formula that would return the total number of hours worked per employee per month (sum the weekly numbers) to fill in the lower table. I have tried combining the XLOOKUP and SUMIFS formula but so far no luck. Does anyone have any tips by chance?

With Excel for MS365, one possible single-cell array formula could be:

=LET(
    table, A1:J5,
    names, TAKE(DROP(table, 1),, 1),
    levels, INDEX(DROP(table, 1),, 2),
    row_labels, names & "|" & levels,
    col_labels, EOMONTH(--DROP(TAKE(table, 1),, 2), 0),
    values, DROP(table, 1, 2),
    arr, LAMBDA(n, CHOOSE(n, row_labels, UNIQUE(SORT(col_labels,,, 1), 1))),
    results, MAP(arr({1}), arr({2}), LAMBDA(r,c, SUM(FILTER(FILTER(values, row_labels = r), col_labels = c)))),
    VSTACK(
        HSTACK(TAKE(table, 1, 2), TEXT(arr(2), "mmmyy")),
        HSTACK(names, levels, results)
    )
)

Adjust the table range reference as needed, and change the output format of the month-end labels as desired (e.g. "mmmm" instead of "mmmyy").

Alternatively, you could use SUM((row_labels = r)*(col_labels = c)*values) instead of SUM(FILTER(FILTER(values, row_labels = r), col_labels = c)), if preferred.

Also, row_labels could be defined as SEQUENCE(ROWS(names)) instead of names & "|" & levels in this particular scenario. Then, you could use SUM(FILTER(CHOOSEROWS(values, r), col_labels = c)) or SUM(CHOOSEROWS(values, r)*(col_labels = c)).

EDIT: In its simplest form, the above-mentioned formula could be reduced to the following:

=LET(
    table, A1:J5,
    col_labels, EOMONTH(--DROP(TAKE(table, 1),, 2), 0),
    values, DROP(table, 1, 2),
    arr, LAMBDA(n, CHOOSE(n, SEQUENCE(ROWS(values)), UNIQUE(SORT(col_labels,,, 1), 1))),
    results, MAP(arr({1}), arr({2}), LAMBDA(r,c, SUM(FILTER(CHOOSEROWS(values, r), col_labels = c)))),
    HSTACK(TAKE(table,, 2), VSTACK(TEXT(arr(2), "mmmyy"), results))
)

Explanation of arr({1}):

Basically, CHOOSE was used as a custom LAMBDA function to simulate a 3D array. When an array object is passed to the index_num argument of CHOOSE (e.g. {1} instead of 1), all of the value arguments are automatically resized via broadcasting. A value argument containing a single column of data (vertical vector) is broadcast across to fill the same number of columns as the argument with the most columns, whereas a value argument containing a single row of data (horizontal vector) is broadcast down to fill the same number of rows as the argument with the most rows. The MAP function is then used to loop through the layers together, left-to-right, top-to-bottom.

Note: in complex scenarios, this method can be more efficient than the MAKEARRAY function (with INDEX), which is known to perform very poorly on larger datasets. However, in this relatively simple scenario, a MAKEARRAY equivalent (without having to INDEX the col_labels) could be:

=LET(
    table, A1:J5,
    col_labels, EOMONTH(--DROP(TAKE(table, 1),, 2), 0),
    periods, UNIQUE(SORT(col_labels,,, 1), 1),
    col_ID, XMATCH(col_labels, periods,, 2),
    values, DROP(table, 1, 2),
    results, MAKEARRAY(ROWS(values), COLUMNS(periods), LAMBDA(r,c, SUM(FILTER(CHOOSEROWS(values, r), col_ID = c)))),
    HSTACK(TAKE(table,, 2), VSTACK(TEXT(periods, "mmmyy"), results))
)