how can i make google sheet itrate over columns

Question

I have a sheet like this how can make a cell in front of x under the sum column get the sum of the x count column and y get the sum y count column of course, I use sum function on both but the issue I face is how to make the z ,x1,y1,z1 the same I try to fill it down but as you see in the picture it is wrong how can I do it for 100 row ?

question from:https://stackoverflow.com/questions/65854905/how-can-i-make-google-sheet-itrate-over-columns

Answer 1

This formula seems to give the result you want:

={"Header","Sum";
  ARRAY_CONSTRAIN(TRANSPOSE(D1:1),COUNTA(D1:1),1),
    ARRAYFORMULA(MMULT(TRANSPOSE(N(D2:AJ10)),{SEQUENCE(ROWS(D2:AJ10),1,1,0)}))}

It places the two column labels in the first row, then transposes all of the header values into a vertical column in A2:A, but prevents any blank rows by using ARRAY_CONSTRAIN, and a check for the number of header values to transpose.

The main result is the Sums, calculated using MMULT. You need to enter the range of the cells you are going to sum over - I've used D2:AJ10, entered twice in the formula. MMULT can slow down performance the more cells it has to review, but this seemed fine for 33 columns by 9 rows. Test it out in your actual sheet, and report back if any issues.

REFERENCES:

ARRAY_CONSTRAIN To limit size of an array result, by # rows and # columns

MMULT The matrix product of two matrices. Can be used for summing, if one matrix is one dimensional (eg. a row or a column) with values of just 1.