Excel: how to sum with common header unit-CodePudding

If I have a *.csv like

aaa,10,0,-200
sss,10,440,0
sss,10,880,0
sss,10,440,0
sss,10,440,0
ddd,5,560,0
ddd,5,560,1560
ddd,5,0,780

Now I want to sum column c and d in terms of column a, and column b remains the default. That is to say, I want to get result like

aaa,10,`=sum(c1:c1)`,`=sum(d1:d1)`
sss,10,`=sum(c2:c5)`,`=sum(d2:d5)`
ddd,5,`=sum(c6:c8)`,`=sum(d6:d8)`

But I dont want type in each formula by hand, so is there any automatical ways to do it?

CodePudding user response：

Try the following formula-

=LET(input,UNIQUE(A1:B8),HSTACK(input,BYROW(input,LAMBDA(x,SUMIFS(C:C,A:A,INDEX(x,1,1),B:B,INDEX(x,1,2)))),BYROW(input,LAMBDA(x,SUMIFS(D:D,A:A,INDEX(x,1,1),B:B,INDEX(x,1,2))))))

CodePudding user response：

This is the exact reason why the "Subtotals" feature has been written. Hereby how the result looks like (you do need to add a heading first to each column):

By clicking on the numbers in the margin (1, 2, 3) you can choose the depth of what you wish to see.