Saturday, November 24, 2012

yogi_Group Data By Customer DayOfOrder And Quantity And Include Interior Columns As Specified

                                          Google Spreadsheet   Post  #887
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI    Nov 24, 2012
user thangle said:(!category-topic/docs/spreadsheets/sv6CWFKl7TA)
Sum or Subtotal using multiple conditions with Arrayformula
Hi guys,

I have a detailed list of product that my customers has ordered. Each of product is has the basic of informations: customer, day of order, productID, quantity.

ColumnA         ColumnB   ColumnC ColumnD         ColumnE
Customer Day of order   No      Product code Quantity
A Nov-1 Product01                                   7
A Nov-1 Product02                                   5
A Nov-1 Product03                        30
A Nov-1 Product04                        10
B Nov-3 Product02                         5
B Nov-3 Product03                                  25

My goal is to have summary table that list all the order by customer and the day-of-order, with total quantities of each order. 
ColumnG         ColumnH   ColumnI ColumnJ ColumnK
Customer   Day of order   No      Note   Quantity
A          Nov-1                            42
B          Nov-3                 30
One simple way is using Google Sheets Pivot table feature, but that's not what I want this time.

My draft function using query() with filter() does work:
L3 = if(G3="","",iferror(sum(query(filter(A$3:E,A$3:A=G3,B$3:B=H3),"select Col5")),""))

But how to achieved that results using Arrayformula() ? I'm still struggling understand MMULT() and can't let it work out. Maybe MMULT() is the solution for me?

Happy weekend!

 following is a solution to the problem