Home > Software design >  Excel, apply sum to certain cells over multiple columns
Excel, apply sum to certain cells over multiple columns

Time:12-26

Excel, apply sum to certain cells over multiple columns

   A, B, C , D
A, 1, 2, 3 , 4
B, 1, 2, 3 , 4
C, 1, 2, 3 , 4
D, =sum(A1,A2,A3)
so A4=3, 

How can we apply the sum to the 4 ofB, C,D over B,C D columns respectively?

CodePudding user response:

You'll need dynamic arrays to have a single formula spilling all results. So, if you have access to BYCOL(), use:

enter image description here

Formula in A4:

=BYCOL(A1:D3,LAMBDA(a,SUM(a)))

Or, else:

=TRANSPOSE(MMULT(TRANSPOSE(A1:D3),SEQUENCE(ROWS(A1:D3),,,0)))
  • Related