Spreadsheet: 883965d1732777175-lambda-and-xlookup-issue-jp-bagel-excel-sumif-let-riddle-spill-365-aligw
Sheet: Sheet1

Raw data |  |  |  |  |  |  |  | Named ranges
Fruits | 2025-01-01 00:00:00 | 2025-02-01 00:00:00 | 2025-03-01 00:00:00 | 2025-04-01 00:00:00 | 2025-05-01 00:00:00 |  |  | References | Name |  |  |  |  |  |  |  |  |  |  | d - FILTER(tblDataBody,[NAME]=y)
Apples | 2229 | 2366 | 2623 | 2878 | 3001 |  |  | A2:F13 | tblData |  |  |  |  |  |  |  |  |  |  | Filters the table column by column (here the first column)
Pears | 700 | 849 | 1140 | 1294 | 1568 |  |  | B3:F13 | tblDataBody
[NAME] | 0 | 0 | 0 | 0 | 0 |  |  | B2:F2 | lstMonths |  |  |  |  |  |  |  |  |  |  | 700
Bananas | 176 | 184 | 219 | 224 | 226 |  |  | A3:A13 | lstFruits |  |  |  |  |  |  |  |  |  |  | 0
Peaches | 7 | 8 | 9 | 10 | 10 |  |  | A17:[NAME] | tblBundles |  |  |  |  |  |  |  |  |  |  | 176
Cherries | 296 | 207 | 205 | 217 | 222 |  |  | A11:A19 | lstBundles |  |  |  |  |  |  |  |  |  |  | 7
Tomatoes | 155 | 158 | 161 | 164 | 168 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 296
Dates | 383 | 390 | 397 | 444 | 451 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 155
Nuts | 0 | 23 | 46 | 35 | 89 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 383
Raspberries | 4511 | 5622 | 7732 | 8966 | 11132 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0
Strawberries | 11877 | 12088 | 15770 | 16041 | 18154 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 4511
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 11877
Bundles |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | b - FILTER(B17:E19,lstBundles=r)
Name | Includes |  |  |  |  |  |  | 2025-01-01 00:00:00 | 2025-02-01 00:00:00 | 2025-03-01 00:00:00 | 2025-04-01 00:00:00 | 2025-05-01 00:00:00 |  |  | 2025-01-01 00:00:00 | 2025-02-01 00:00:00 | 2025-03-01 00:00:00 |  |  | Pulls the bundles row by row (here the first row)
Red | Strawberries | Raspberries | Cherries | Tomatoes |  |  | Red | 16839 | 18075 | 23868 | 25388 | 29676 |  | Red | 16839 | 18075 | 23868 |  |  |  | Raspberries | Cherries | Tomatoes
Yellow | Pears | Apples | Peaches |  |  |  | Yellow | 2936 | 3223 | 3772 | 4182 | 4579 |  | Yellow | 2936 | 3223 | 3772 |  |  | BYROW(A29:[NAME],
Dry | Nuts | [NAME] | Dates |  |  |  | Dry | 383 | 413 | 443 | 479 | 540 |  | Dry | 383 | 413 | 443 |  |  | Sets the parameter r (values from A29:A31)
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | DROP(REDUCE("",B28:D28,LAMBDA(x,y,HSTACK(x,
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Sets the parameter y (values from B28:D28)
Reports |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | SUM(FILTER(d,ISNUMBER(FIND(lstFruits,TEXTJOIN(" ",,b)))))
Bundle | 2025-01-01 00:00:00 | 2025-02-01 00:00:00 | 2025-03-01 00:00:00 |  | <- This solution works great enough, but I am looking to simplify it even more. |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Builds the results matrix row by row and column by column (here the first cell of the resulting matrix)
Red |  |  |  |  | =SUM(SUMIFS(B$4:B$14,lstFruits,FILTER(tblBundles,lstBundles=$A25,0)))
Yellow |  |  |  |  | Look in January data,in the list of fruits and sum if fruit is included in the bundle filtered to the bundle color. |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Without DROP
Dry |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 16839 | 18075 | 23868
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | #N/A | 2936 | 3223 | 3772
Bundle | 2025-01-01 00:00:00 | 2025-02-01 00:00:00 | 2025-03-01 00:00:00 |  | <- I'd love to have a clean LET function, pass the ranges for months and bundle names to a LAMBDA function |  |  |  |  |  |  |  |  |  |  |  |  |  |  | #N/A | 383 | 413 | 443
Red |  | 18075 | 23868 |  | and spill the values in the table. I banged my head on the SUMIF only accepting ranges issue for days…
Yellow | 2936 | 3223 | 3772 |  | Can I use the same FILTER approach, and use a LAMBDA to SUM all the XLOOKUP values for each fruit? |  |  |  |  |  |  |  |  |  |  |  |  |  |  | VSTACK("Bundle",lstBundles)
Dry | 383 | 413 | 443 |  | I've failed so far… |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Creates the row labels column
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Red
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Yellow
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Dry