In cell B1, how to write a Query Function to filter sales from "Data" sheet, When I paste any salesman list into A1:A9999 , it would shows sales amount for each salesman.

The result should be as follow:

CodePudding user response：

Here's another solution:

={"Sales"; arrayformula(if(A2:A<>"",iferror(vlookup(A2:A,query(Data!A1:E,"Select B, sum(E) where B is not null group by B label sum(E) ''",0),2,false),"Not Found"),""))}

• The difference for this formula is that it will return your selected salesman's total sale (if ever your data will have multiple salesman entries)

Example: (I modified your data sheet for this example)

Output: