Home > OS >  Using Query to replace PIVOT
Using Query to replace PIVOT

Time:04-15

Since Query is such a powerful formula, i try to avoid my team members to avoid messing up the pivot. I try to to come out with query to replace my pivot. Based on the raw data enter image description here

CodePudding user response:

I created a new tab and entered the following in cell A1:

=query(
  {'RAW DATA'!B:B,'RAW DATA'!E:E,'RAW DATA'!G:J},
  "select Col1, sum(Col3), sum(Col4), sum(Col5), sum(Col6) 
   where Col1 is not null and Col2<>'Black and Yellow' 
   group by Col1"
)

enter image description here

CodePudding user response:

Use a query() with a group by clause, like this:

=query( 
  'RAW DATA'!B1:J, 
  "select B, sum(G), sum(H), sum(I), sum(J) 
   where B is not null and E <> 'Black' and E <> 'Yellow' 
   group by B", 
  1 
)

The result table will only include weeks where there is at least one data row where E is not Black or Yellow.

See the new Solution sheet in your sample spreadsheet.

  • Related