Google sheet - how to filter the data with custom date range

Question

I know this can be achieved with pivot in google sheet but it wont let us to select the date range to fetch the results in pivot. What i need is when I select the date range and agent in sheet 2 it returns me the result with total uploaded qty.

My Data Set in Sheet 1

Result Required in Sheet 2

Any help will be highly appreciated.

Answer 1

try:

={QUERY(Data!A:D, 
 "where A = '"&C2&"' 
    and B >= date '"&TEXT(A2, "yyyy-mm-dd")&"' 
    and B <= date '"&TEXT(B2, "yyyy-mm-dd")&"'", 1); "","", "Total", 
 IFNA(SUM(QUERY(Data!A:D, 
 "select D 
  where A = '"&C2&"' 
    and B >= date '"&TEXT(A2, "yyyy-mm-dd")&"' 
    and B <= date '"&TEXT(B2, "yyyy-mm-dd")&"'", 0)), "")}