Use QUERY Formula in Google Sheet as Replacement of PivotTable Function
Second case will show when grouping rows from multiple columns.

The formula shown above:=QUERY(A2:C51,"select A, C, sum(B) where A is not null or B <> 0 group by A, C label A 'Cable Size', C 'Cable Type', sum(B) 'Length (m)'")
As we can see, if we want to group by 2 or more column (ex: group by A, C
), we shall also select those column (ex: select A, C
).
Last case will show how to look like a pivot table.

The formula shown above:=QUERY(A2:C51,"select A, sum(B) where A is not null or B <> 0 group by A pivot C label A 'Cable Size'")
As we can see, if we want one of the column data to be the row header, use pivot function (ex: pivot C
) and remove that column from select.
That’s it. Did you find this formula easier & more practical than PivotTable function?
Attention:
There shall be no any content in cells below cell that contain QUERY formula and cells affect by it, or the QUERY function will shown error.