Google Spreadsheet Vlookup ImportRange Arrayformula Sumif

Question:

( by ZackC )

I have a spreadsheet that needs quite a few Query calls,176 and more all the time.

the query I'm using is:

=SUM(Query('Import'!$A$4:$H$999, "Select C, E where C = '" & B2 & "'"))

What I need it to do is check every row from 'Import' to match B2 from the current sheet and add col E together from 'Import'. (Its for a store inventory receivables putting them into inventory)

Is there a way to make this simpler and less complex for every row so I can keep adding to 'Import' without serious lag?

=======

Yes, I suppose this would need an example. I was hoping for a quick fix as this project is just temporary until I get a chance to sit down and make a full system in C.

The relevant spreadsheets:

The actual Master Inventory sheet contains about 250 items and are imported by columns into the Purchase Order sheet for reference. Then each individual line item is enter into that sheet, getting details via vLookup on the other sheet, which then counts how many of each SKU has been ordered so it can then be thrown back (not done yet) into the master inventory as a total count. A little spaghetti spreadsheeting and convoluted but it isn't going to last long(tm). (With my luck I'll never have time to write a proper program)

Solution:

Note you have used ImportRange and Vlookup many times, which can be reduced to just one time... And Query formula can be replaced with Sumif and Arrayformula..

Have a look at the following screenshot of Spreadsheet "Inventory - Purchase Orders" Sheet "Data":

In above sheet, I have the following formula in Cell A1:
=query(ImportRange("0AjFhwhh41YUwdE9UV216bkFBcVZoR3h3djZfSnQ5Z1E", "Inventory!A:T");"select Col15,Col1,Col2,Col16 where Col1<>'' ")

and the following formula in Cell E1:
=arrayformula(if(row(A:A)=1;"Count from Input";if(A:A="";"";sumif('Import'!C:C;B:B;'Import'!E:E))))


Have a look at the following screenshot of Spreadsheet "Inventory - Purchase Orders" Sheet "Import":

In above sheet, I have the following formula in Cell C3:

=arrayformula(iferror(vlookup(F3:F,Data!A:E,{2,3}*sign(row(F3:F)),FALSE)))

So now you will have very less number of formulas in your spreadsheet, which is going to reduce spreadsheet complexity and increase the performance and loading speed.

And If you are not much familiar with scripts then check out the following link:
http://igoogledrive.blogspot.com/2012/08/how-to-write-script-in-google.html 

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive