Sum values based on XLOOKUP

Question

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?

Resource Name	Resource Level	7/22/2024	7/29/2024	8/5/2024	8/12/2024	8/19/2024	8/26/2024	9/2/2024	9/9/2024
Rob	Manager	40	20	37	29	23	31	33	32
Tom	Analyst	30	25	26	27	19	39	19	31
Jessica	Senior Analyst	20	34	30	35	34	30	29	24
Julia	Business Analyst	15	34	28	22	27	36	38	19

Resource Name	Resource Level	July	August	September
Rob	Manager
Tom	Analyst
Jessica	Senior Analyst
Julia	Business Analyst

Answer 1

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))
)