Forum Discussion
Two way sum lookup (by month & name)
Another option for modern Excel:
=LET( rϑws, MAP(A12:A15, LAMBDA(v, LET(x, XLOOKUP(v, A4:A7, B4:W7, B2:W2), LAMBDA(x)))), cϑls, MAP(B11:G11, EOMONTH(+B11:G11, 0), LAMBDA(s,e, LAMBDA(n, CHOOSE(n, s, e)))), MAP(IFNA(rϑws, cϑls), IFNA(cϑls, rϑws), LAMBDA(r,c, SUMIFS(r(), B3:W3, ">=" & c(1), B3:W3, "<=" & c(2)))) )This method uses the concept of "thunks" to store the applicable row of data for each name, as well as the applicable start and end dates for each period, inside a separate LAMBDA function. The two resulting vectors of "thunks" are then broadcast both across and down to fill an array for the entire output grid, and MAP loops through both arrays together, applying SUMIFS to each applicable row for each applicable period.
Alternatively, GROUPBY could also be used, but the order of names will not be exactly the same as the desired output table shown in your sample file:
=LET( tbl, TRANSPOSE(A3:W7), mth, IFERROR(EOMONTH(TAKE(tbl,, 1), 0), "Name"), arr, TRANSPOSE(GROUPBY(mth, DROP(tbl,, 1), SUM, 3, 0)), VSTACK(TEXT(TAKE(arr, 1), "mmm-yy"), DROP(arr, 1)) )Cheers!
Another option for modern Excel:
=LET(
rϑws, MAP(A12:A15, LAMBDA(v, LET(x, XLOOKUP(v, A4:A7, B4:W7, B2:W2), LAMBDA(x)))),
cϑls, MAP(B11:G11, EOMONTH(+B11:G11, 0), LAMBDA(s,e, LAMBDA(n, CHOOSE(n, s, e)))),
MAP(IFNA(rϑws, cϑls), IFNA(cϑls, rϑws), LAMBDA(r,c, SUMIFS(r(), B3:W3, ">=" & c(1), B3:W3, "<=" & c(2))))
)This method uses the concept of "thunks" to store the applicable row of data for each name, as well as the applicable start and end dates for each period, inside a separate LAMBDA function. The two resulting vectors of "thunks" are then broadcast both across and down to fill an array for the entire output grid, and MAP loops through both arrays together, applying SUMIFS to each applicable row for each applicable period.
Alternatively, GROUPBY could also be used, but the order of names will not be exactly the same as the desired output table shown in your sample file:
=LET(
tbl, TRANSPOSE(A3:W7),
mth, IFERROR(EOMONTH(TAKE(tbl,, 1), 0), "Name"),
arr, TRANSPOSE(GROUPBY(mth, DROP(tbl,, 1), SUM, 3, 0)),
VSTACK(TEXT(TAKE(arr, 1), "mmm-yy"), DROP(arr, 1))
)Cheers!
One more dynamic array option, using MMULT:
=MMULT(--(A12:A15=TOROW(A4:A7)),MMULT(--B4:W7,--(EOMONTH(+B11:G11,0)=TOCOL(EOMONTH(+B3:W3,0)))))Plus, an improved variation of my previous GROUPBY suggestion:
=LET(
tbl, TRANSPOSE(A3:W7),
arr, TRANSPOSE(GROUPBY(EOMONTH(TAKE(tbl,, 1), 0), DROP(tbl,, 1), SUM, 3, 0)),
VSTACK(IFERROR(TEXT(TAKE(arr, 1), "mmm-yy"), "Name"), DROP(arr, 1))
)