how to write a Query Function to Summarize some info from "Data" sheet-CodePudding

In cell B1, how to write a Query Function to Summarize some info from "Data" sheet, When I paste any Product Serial number into A2:A9999 , it would shows how many "Returns" and how many "Order Placed" for this Product Serial number.

The result should be as follow:

https://docs.google.com/spreadsheets/d/112bNvwv6shGZkHi1MJcWLsuM--mf57MEdqcMjZCQwBc/edit?usp=sharing

Thank you so much for your help.

CodePudding user response：

You don't necessarily need QUERY.

I've added a new sheet ("Erik Help") with the following formula in B1:

=ArrayFormula({"Summary"; IF(A2:A="",, "Returns:"&COUNTIFS(Data!B2:B,A2:A,Data!F2:F,"*Return*")&" | Orders Placed:"&COUNTIF(Data!B2:B,A2:A))})

This formula generates the header and all column results using fairly straightforward COUNTIFS.