I have a table with multiple rows and columns (hundreds), and to make it simple, it looks pretty much like this:
| Name/Date | 01/22 | 02/22 | 03/22 | 04/22 | 05/22 |
|---|---|---|---|---|---|
| Jack | 10 | 4 | 7 | 1 | 10 |
| Rose | 6 | 4 | 2 | 3 | 3 |
| Mary | 2 | 6 | 7 | 2 | 9 |
| Juan | 9 | 8 | 1 | 5 | 10 |
| Rose | 10 | 8 | 5 | 7 | 1 |
| Juan | 6 | 4 | 2 | 5 | 9 |
| Jack | 10 | 7 | 7 | 4 | 5 |
| Mary | 5 | 1 | 1 | 2 | 3 |
| Rose | 2 | 4 | 4 | 1 | 1 |
My objective is to create an array formula that sums the values for each unique name and for each date, so in the end it should look like this:
| Name/Date | 01/22 | 02/22 | 03/22 | 04/22 | 05/22 |
|---|---|---|---|---|---|
| Jack | 20 | 11 | 14 | 5 | 15 |
| Rose | 18 | 16 | 12 | 11 | 5 |
| Mary | 7 | 7 | 8 | 4 | 12 |
| Juan | 15 | 12 | 3 | 10 | 19 |
The restriction is that I have to get this output with functions in just one cell, and without helper columns.
Although it is not so difficult doing this using a combination of =HSTACK() and =SUMIF(names,UNIQUE(names),values), with a different SUMIF for each date, it is not possible in this project because of the great number of columns I have to deal with and because the number of columns varies with each analysis.
I tried solving this with a combination of =SUMIF(names,UNIQUE(names),values) and lambda Array Formulas (Like BYROW, BYCOL and MAP, but I wasn't able to get anything but an error (BYROW can only return 1 column as output, BYCOL can only return 1 row, and MAP function returns an array of the same size as the input). What can be done to solve this?
=LET(tData,A1:F10,
Data,DROP(tData,1),Headers,TAKE(tData,1),Names,TAKE(Data,,1),Values,DROP(Data,,1),
uNames,UNIQUE(Names),uMatches,XMATCH(Names,uNames),
cResult,DROP(REDUCE("",SEQUENCE(ROWS(uNames)),LAMBDA(uRow,uMatch,
LET(rFilter,uMatches=uMatch,
rResult,BYCOL(Values,LAMBDA(vCol,SUM(FILTER(vCol,rFilter)))),
VSTACK(uRow,rResult)))),1),
VSTACK(Headers,HSTACK(uNames,cResult)))