CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulagoogle-query-language
allison
allison

Reputation: 187

Google Sheets - Query Table with output corresponding to specific cell criteria

I am trying to create a query based on a date range, that will display output based on the values in another column.

Here is the sample dataset I'm working with.

I would like the # Allotted (Column F) to be queried into 2 separate columns, depending on whether the Cost = 0. If the Cost = 0, I want the # Allotted to be listed under column "Free Trial" - otherwise, it should be listed under "Purchased."

I tried to create 2 separate queries for the "Purchased" and "Free Trial" columns but I can't figure out how to tell it to list the output based on a key value, such as Customer.

You can see my attempt in the sheet attached as well as what I'd like the output to look like. I highlighted the columns I'm having trouble with.

Thank you for your help!

enter image description here

Upvotes: 0

Views: 95

Answers (2)

allison
allison

Reputation: 187

I figured it out! Ended up using Filter instead of Query so I could filter based on the criteria selected.

I updated the solution to the sheet linked in the question.

For column "Purchased":

=iferror(FILTER(F4:F14,A4:A14=G19,J4:J14>=A$2,J4:J14<=B$2,G4:G14<>0),"")

For column "Free Trial":

=iferror(FILTER(F4:F14,A4:A14=G19,J4:J14>=A$2,J4:J14<=B$2,G4:G14=0),"")

Upvotes: 0

Aresvik
Aresvik

Reputation: 4620

Try:

={query(
{query({A:J},"select Col1,Col3,Col9,Col6,Col7 where Col7 =0 ",1);
query({A:J},"select Col1,Col3,Col6,Col9,Col7 where Col10 > date '"&text(A2,"yyyy-mm-dd")&"' and Col10 < date '"&text(B2,"yyyy-mm-dd")&"' and Col7>0  ",1)}
,"where Col2 is not null order by Col3,Col4 label Col1 'Customer',Col2 'Type', Col3 'Purchased', Col4 'Free Trial', Col5 'Cost'",1)}

Upvotes: 1

Related Questions