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

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

CodePudding user response：

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)}

CodePudding user response：

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),"")