Search code examples
excelms-office

Sum the values in respective columns if the value in first column are same in MS Excel

Need to get the total sum at the bottom for each value in the first row if they are same. I tried Subtotal(9,range) but this requires lot of manual work. I have around 1000+ rows which needs to be summed.

Example of the problem in the picture below. (colored cells)

enter image description here

Solution

  • Use SUM() with FILTER(). BYROW() is for dynamic spill array to iterate criteria's.

    =BYROW($A$13:$A$15,LAMBDA(x,SUM(FILTER(B$2:B$9,$A$2:$A$9=x))))

    enter image description here

    All in one formula-

    =LET(
id,UNIQUE(A2:A9),
x,MAKEARRAY(ROWS(id),COLUMNS(B2:D9),LAMBDA(r,c,SUM(CHOOSECOLS(FILTER(B2:D9,A2:A9=r),c)))),
HSTACK(id,x))

    enter image description here