SUMIF with UNIQUE criteria for multiple columns, adding all values with same name

Question

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?

You would have a better chance of someone helping you if you would use markdown tables instead of photos so we can copy paste. — Scott Craner, Feb 24 '23 at 22:46
Why not just use the in-built Consolidate function (Data tab)? Ideal for this. — Jos Woolley, Feb 25 '23 at 07:46
Main Issue in this case is automation and the fact that all this calculation is just an intermediary step, but in other cases this would be a perfect solution — Paulo Jose de Camargo Neto, Feb 27 '23 at 17:29

score 2 · Accepted Answer · answered Feb 25 '23 at 03:38

Sum If Unique

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

David Leal · Answer 2 · 2023-02-25T04:57:56.080

You can try the following array formula that spills the entire result including the header. Put in cell H1, the following:

=LET(in,A1:F10,n,DROP(TAKE(in,,1),1),nUx,UNIQUE(n),h,TAKE(in,1),d,DROP(in,1,1),
 SUMBYROW,LAMBDA(y,BYROW(nUx, LAMBDA(ux,SUM(FILTER(CHOOSECOLS(d,y),n=ux))))),
 cal,REDUCE(nUx,SEQUENCE(COLUMNS(h)-1),LAMBDA(ac,hh,HSTACK(ac,SUMBYROW(hh)))),
 VSTACK(h, cal))

Here is the output:

It uses the REDUCE/HSTACK pattern to concatenate the column result on each iteration of the REDUCE function. For more information check my answer to the following question: how to transform a table in Excel from vertical to horizontal but with different length. In this case, the accumulator (ac) in REDUCE can be initialized with the list of unique names (nUx).

The formula only depends on the range defined in the name in(input), so it is easier to maintain. The other names are defined from there (n-names, nUx-unique names, d-data, h-header), using DROP, UNIQUE and TAKE functions. We define a user LAMBDA function SUMBYROW for a better composition, so it is easier to understand the mentioned pattern. We invoke on each iteration this function on each column index.

SUMIF with UNIQUE criteria for multiple columns, adding all values with same name

2 Answers2

Sum If Unique