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

Question

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!

Answer 1

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