CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Terence Sweeney
Terence Sweeney

Reputation: 61

How do I return a sorted array of the sums of columns (grouped by a column)

I have a sheet of data and a formula which, for each line, returns an array to column H, of column headers sorted according to the data in the line (highest to lowest).

Here is the formula I use. It gets the job done.

=TRANSPOSE(QUERY(TRANSPOSE({$B$1:$G$1;$B2:$G2}),"SELECT Col1 ORDER BY Col2 DESC",0))

In column N, I now want to return another array of the column headers, however this time according to the total of all the lines which have the same "code" in column A (again sorting the column headers from highest to lowest). This means row 4, column N will sort the headers according to the sum of row 2 and row 4 (as each of these lines has the same code in column A, being A1).

I would like to find a solution that doesnt need to declare the sum of each column explicitly as the final spreadsheet will have about thirty columns.

I am struggling to find a way to do this. I am happy to try a completely different solution if needed.

Any suggestions?

Here is a link to the sheet... https://docs.google.com/spreadsheets/d/15Y06GIY0HYGzOVRS-a_1SYWzVq5dBffHBnmg5k6hCxQ/edit?usp=sharing

I tried to add the code column to the Query statement however this doesn't work as, when the data is transposed, it results in the columns having two different data types.

Upvotes: 0

Views: 227

Answers (2)

Terence Sweeney
Terence Sweeney

Reputation: 61

Okay... so after much research and playing around I managed to create a solution using the MMULT formula.

Here is my final formula. I have also posted the working version in the original shared spreadsheet.

=TRANSPOSE(QUERY(TRANSPOSE({$B$1:$G$1;mmult(transpose(ArrayFormula(row($A$2:$A)^0)),ARRAYFORMULA(($A$2:$A=$A2)*$B$2:$G))}),"SELECT Col1 ORDER BY Col2 DESC",0))

If anyone else wants to understand the structure of the MMULT part of the formula, here is a link to the post that led me to this solution.

https://infoinspired.com/google-docs/spreadsheet/mmult-instead-of-sumif-in-google-sheets-for-array-result/

Much appreciation to those who contributed.

Upvotes: 0

player0
player0

Reputation: 1

paste in N2 cell and drag down to N3 cell:

=TRANSPOSE(QUERY(TRANSPOSE(QUERY(QUERY(A$1:G,"where A='"&A2&"'"), 
 "select sum(Col2),sum(Col3),sum(Col4),sum(Col5),sum(Col6),sum(Col7) 
  where Col1 is not null 
  group by Col1 
  label sum(Col2)'"&B$1&"',sum(Col3)'"&C$1&"',sum(Col4)'"&D$1&"',sum(Col5)'"&E$1&"',sum(Col6)'"&F$1&"',sum(Col7)'"&G$1&"'", 1)),
 "select Col1
  order by Col2 desc"))

0

Upvotes: 0

Related Questions