## Monday, February 2, 2015

### yogi_Compute Sum OF Qty By Item From A Table Containing Multiple (indefinite) Sets Of Qty And Item Columns

Google Spreadsheet   Post  #1887
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Feb-02-2015
post by  simojgd:
https://productforums.google.com/forum/#!mydiscussions/docs/hbhNwXq_NFE
Array formula/ query query
Hi, I have an INVENTORY sheet that has different invoice details by row with 'number sold' and 'item' in consequetive  columns, starting with Col U Ex:

 U #VALUE! W X Y Z Quantity 4 Item 4 Quantity 5 Item 5 100 BDM-250 50 AC Intercon Cable 50 End Cap 130 AXN6P610T250BW 130 AXN-P6T270BB 36 AXN6P610T250BW 36 BDM-250 1 BDG-256 3 AC Intercon Cable 3 End Cap 50 TS-S420 3 AC Intercon Cable 600 MR-SW-HP-35S 50 AXN6P610T250SW 3 End Cap 600 BDM-250 3 AC Intercon Cable 24 BDM-250 3 AXN6P610T250BW 22 BDG-256 3 End Cap

I want to filter each unique item with the SUM of the sales across all rows. Although there may be a more elegant way to do this for col U-EF, I can only think of filtering per column. like this:
In V1, in order to calculate the total sales for each  unique item, I am using:

=ArrayFormula(QUERY(IF({1,0},IFERROR(REGEXEXTRACT(V2:V,"^([a-zA-Z]+)")),Inventory!B2:B),"select Col12, sum(Col1) where Col2 != '' group by Col2 label sum(Col1) ''",0))

but it is not working. If anyone can help me, I would be most grateful.

Here is a link to the sheet.
and it is the Expense tab
Thanks in advance for any advise!
-------------------------------------------------------------------------