CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaarray-formulas
Fabian
Fabian

Reputation: 55

Summation of columns in matrix into an array

I need a formula that takes the values from a matrix, sums the columns and returns an array (the rows).

SUM() only seems to return a single value when summing both matrices and arrays, and my formula needs to return an array. It also needs to fit inside a single cell. For you to get an idea of what I'm looking for, if I were only using two arrays, my code would look like:

=ARRAYFORMULA(A1:A10+B1:B10)

However, I need to sum an undefined amount of arrays element-wise, and not end up with a single value.

Upvotes: 1

Views: 477

Answers (1)

player0
player0

Reputation: 1

formulas for the multi-column sum are:

=ARRAYFORMULA(SUMIF(IF(COLUMN(A1:Z1), ROW(A1:A1000)), ROW(A1:A1000), A1:Z1000))

=ARRAYFORMULA(MMULT(IFERROR(VALUE(A1:Z1000), 0), TRANSPOSE(COLUMN(A1:Z1000)^0)))

if the last column is dynamic you will need to do it like this:

=ARRAYFORMULA(MMULT(IFERROR(VALUE(INDIRECT("A1:"&ADDRESS(1000, COLUMNS(A:AAA), 4))), 0), 
 TRANSPOSE(COLUMN(INDIRECT("A1:"&ADDRESS(1000, COLUMNS(A:AAA), 4)))^0)))

_______________________________________________________________

0

cell D2:

=ARRAYFORMULA(IF(LEN(INDIRECT("A2:A"&COUNTA(A2:A)+1)); 
 SUBSTITUTE(TRANSPOSE(QUERY(TRANSPOSE(F2:I16&":");;999^99)); " "; ""); ))

cell E19:

=ARRAY_CONSTRAIN(ARRAYFORMULA(SPLIT(FILTER(INDIRECT("D2:D"&COUNTA(A2:A)+1); 
 COUNTIF(B19:B; INDIRECT("A2:A"&COUNTA(A2:A)+1))); ":"; 1; 0)*C19:C); 
 COUNTA(B19:B); COUNTA(E18:18))

cell J20:

=ARRAYFORMULA(JOIN(":"; MMULT(
 TRANSPOSE(INDIRECT("E19:"&ADDRESS(21; COLUMN()-2; 4))); 
 TRANSPOSE(SPLIT(REPT(10; COUNTA(E19:E)); 1))^0)))&":"

note: COLUMN()-2 means that the sum is located two columns from end of the table

Upvotes: 3

Related Questions