How to sum the maximum value of each column in Google Spreadsheets?

Question

I have a Google Spreadsheet of numbers. How do I take the maximum value from each column, and summarize them using only one formula? (No temp cells, no scripts.)

1  2  1
0  1  3
0  2  0

For the table above the result should be 6 (1+2+3, the maximum value of each column). But I'd like a solution that works for much larger tables, too.

As a more general question, I'd like to find out how I could fold 2D ranges into 1D arrays using an arbitrary operator (like MAX and SUM in this case).

Are you just looking for a basic answer, or an alternate approach? =SUM(MAX(A1:A3), MAX(B1:B3), MAX(C1:C3)) works for your query... — Dave
I'd like an ultimate approach for an arbitrary large table. Thanks! (I edited the question.) — aedm

JPV JPV · Accepted Answer · 2016-06-22T09:32:59

Assuming your data in range A2:D, to get the maximum of every row (array output) try

=query(transpose(query(if(row(A2:D)>=transpose(row(A2:D)),transpose( A2:D)),"select max(Col1),max(Col2),max(Col3),max(Col4) ",0)),"Select Col2", 0)

If you need to process a lot of columns, this may be better

=ArrayFormula(QUERY(TRANSPOSE(QUERY(TRANSPOSE( A2:D) , "Select "&"MAX(Col"&JOIN( ", MAX(Col",ROW(INDIRECT( "YY1:YY"&ROWS(A2:A)))&")"))), "Select Col2", 0))

To sum, just wrap SUM() around the above formulas.

How to sum the maximum value of each column in Google Spreadsheets?

2 Answers