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:
Formula in A4
:
=BYCOL(A1:D3,LAMBDA(a,SUM(a)))
Or, else:
=TRANSPOSE(MMULT(TRANSPOSE(A1:D3),SEQUENCE(ROWS(A1:D3),,,0)))