How to use Query Function to list all Salesman according to the Date Range and Total Sales Range?-CodePudding

For example: From 4/14 to 5/1, List the saleman whose total sales range is within $30 ~ $50

The Result should be: Salesman 0196

(Because his total sales is $47.95 from From 4/14 to 5/1 )

I sincerely need your help to write a Query Function to list all Salesman according to the Date Range and Total Sales Range ? Thank you so much

https://docs.google.com/spreadsheets/d/13pIdJOtshK2mv4B2II8gAjKvL9uIjg87LHzREZNEtbE/edit?usp=sharing

CodePudding user response：

In the spreadsheet you shared I entered this formula

=query(query(A:E, "Select B, sum(E) where A >= date '"&text(I1, "yyyy-mm-dd")&"' and A <= date '"&text(K1, "yyyy-mm-dd")&"' group by B", 0), "where Col2 > "&I2&" and Col2 < "&K2&" order by Col2 desc label Col1 'Salesman', Col2 'Total Sales'", 0)

See if that works for you?