Drag down a sum formula but change column reference

Question

I am trying to drag down a sum formula but change the column reference in stead of row reference.

For example cell

E1 =sum('Sheet2!C3:C)
E2 =sum('Sheet2!D3:D)
E3 =sum('Sheet2!E3:E)

Any help?

Answer 1

For the example you shared, see if this works (in E1)

=ArrayFormula(mmult(transpose(N(Sheet2!C3:E)), row(C3:C)^0))

Change range to suit. This formula outputs an array, so dragging down is not needed.

Answer 2

Here the formula that use query, C & E can be changed to future actual range:

= arrayformula(query(transpose(query(C3:E +0,"select " & join(",","sum(Col" & COLUMN(C3:E3)-Column(C3)+1 & ")") )),"Select Col2"))