Google Sheets sum rows with the same first cell value grouped by first row value

Question

I have dynamic data for an online shop with sales by product, by week split into columns:

Sample Data

I want to create a header row of the unique weeks and summarise the total sales by product by week in a dynamic table using query and or array formula if possible. However, Arrays and Queries seem to be designed for data exclusively in columns so maybe I need to transpose it in some way? Any ideas?

Hi, I've added the how it should look image below in the link Results in my attempt at an answer below — Adam Miller, Mar 05 '19 at 12:28

player0 · Answer 1 · 2019-03-05T12:35:49.110

0

you can do:

=QUERY(B2:E, "select B,C+D,E label C+D''", 0)

or:

=ARRAYFORMULA({IF(B99=C99, B100:B+C100:C, B100:B),
               IF(C99=D99, C100:C+D100:D, C100:C),
               IF(D99=E99, D100:D+E100:E, D100:D),
               IF(E99=F99, E100:E+F100:F, E100:E)})

edited Mar 05 '19 at 12:35

answered Mar 05 '19 at 12:08

player0

124,011
12
67
124

Hi, that's a brilliant way of summing the rows - thanks. However, I need to sum each product by the week it was sold for reporting purposes. This aggregates all sales for each product. – Adam Miller Mar 05 '19 at 12:13

score 0 · Answer 2 · edited Mar 05 '19 at 17:44

Okay, so I took my own advice and did a transpose to get the data into a state that Query can work with and then re-transposed it back to get the format I wanted. However, it's not exactly dynamic as I'd have to edit the formula if we added or took away any products.

=Transpose(query(transpose(A2:E13),"Select Col1, Sum(Col2), Sum (Col3), Sum(Col4), Sum(Col5), Sum(Col6) ,Sum(Col7), Sum(Col8), Sum(Col9), Sum(Col10), Sum(Col11), Sum(Col12) group by Col1",1))

Which produces a nice tabular result:

Results

Any ideas how to make the formula more dynamic?

Google Sheets sum rows with the same first cell value grouped by first row value

2 Answers2