CODEWITHSUNDEEP
if-statementgoogle-sheetsgoogle-sheets-formulaarray-formulas
Toon
Toon

Reputation: 652

Data Cols in Google Sheets Query Formula

What I'm trying to do is to make a query formula returning year sales by week number filtered by store.

Example sheet: Link

It's easy to do with formula like this one: =Query(query(A:D,"Select A,Sum(D) where A is not null group by A Pivot B",1),"Select * offset 1",0)

But I also need to filter results based on specific store (Col C)

It's also not hard: =Query(query(A:D,"Select A,Sum(D) where C = 'First' AND A is not null group by A Pivot B",1),"Select * offset 1",0)

But in this case any week with 0 sales and store equals to 'Second' will be missed.

I would like to show all weeks (Col A) presented in the data. Is it possible?

Upvotes: 0

Views: 56

Answers (1)

player0
player0

Reputation: 1

try:

=ARRAYFORMULA(QUERY(QUERY({A:B, IF(C:C<>"First", {"First", 0}, C:D)}, 
 "select Col1,sum(Col4) 
  where Col3 = 'First' 
    and Col1 is not null 
  group by Col1 
  pivot Col2"), "offset 1", 0))

Upvotes: 2

Related Questions