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.

https://docs.google.com/spreadsheets/d/1e2VVIP7n7FiJmSauqY03urBh5i8DRF8Ln1sCANWRdho/edit?usp=sharing

and it is the Expense tab

Thanks in advance for any advise!

-------------------------------------------------------------------------

yogi_Formula In Cell I11 For Cells I11 To I31 To Pull Values From Column W In Sheet Items -- Adding Week(s) If Looked Up Value Is A Number

                 Google Spreadsheet   Post  #1799

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Oct-19-2014

post by  simonjgd:

(https://productforums.google.com/forum/#!mydiscussions/docs/Ktmab8IgN-U)
VLLOOKUP CODE WRONG

This code:

=VLOOKUP(G11,Items!$R$2:$Y$223,6,IF(W2="Y","In Stock",IF(W2=1,"1 Week",IF(W2=2,"2 Weeks",IF(W2=4,"4 Weeks",IF(W2=8,"8 Weeks"))))),false)

gives this:

Error: Wrong number of arguments to VLOOKUP. Expected between 3 and 4 arguments, but got 5 arguments

Can you advise?

Thanks

---

Hi Yogi,

https://docs.google.com/spreadsheets/d/1REORszzugKu6it7nCARa3JoVPAd7CzzkyM2j3MXyTUU/edit?usp=sharing

cell I11.

Using VLOOKUP, I am trying to populate row I with appropriate data from 'Items!W'

-------------------------------------------------------------------------------------------------