Help wiht multiple lookup criteria from different ranges - (Google Sheets)

S

sheeeets

New Member
Joined
Jul 7, 2015
Messages
3
Hi,

Just finalasing some data from stocktake...

We have large products which some products have multiple cartons per item.... up to 28 cartons to make up on product.

We have about 3000 unique locations which are being counted.

About 1800 unique SKU in total.

I am now trying to pull in a SUM of the total for each carton for each product so I can see what products are missing cartons so we are physically seperate.

I am struggling trying to get the formulas to search for SKU and CARTON and then total the QTY... this is coming from multiple sources as there 3000 locations with each location can have up to 20x counts (eg "WIDGET - Carton 2 of 2 - Qty = 3") in the spreadsheet.

Any help would be amazing.

Below I have included a sample of the source data... and sample of an ideal layout in a seperate sheet.

Also note that the solution MUST be able to work in google docs as many people working on this at once.

Thanks again for any help.

Cheers










F-SM











































F-FID















































VF-RFT15-BF















































VF-RFT15-GF















































F-SQUAT



F-SQUAT



















































































LFAT3SG







LFPACFIB























































































VF-RFT15-RF































































































V-V1200































































































V-V1000































































































AS-AssaultB































































































GSLED































































































V-V1000















































LFPHCT



LFPHCT



LFPAOIB



LFPAOIB



LFPAOIB











VF-RFT15-RF
















































<colgroup><col style="width: 128px"><col width="171"><col width="52"><col width="58"><col width="16"><col width="171"><col width="52"><col width="58"><col width="16"><col width="171"><col width="52"><col width="58"><col width="16"><col width="171"><col width="52"><col width="58"><col width="16"><col width="171"><col width="52"><col width="58"><col width="15"><col width="171"><col width="52"><col width="58"></colgroup><tbody>
[TD="bgcolor: #000000, align: center"]LOCATION ID[/TD]
[TD="bgcolor: #000000"]1st SKU / Carton Number[/TD]
[TD="bgcolor: #000000, align: center"]Carton[/TD]
[TD="bgcolor: #000000, align: center"]QTY 1[/TD]
[TD="bgcolor: #ff0000"][/TD]
[TD="bgcolor: #000000"]2nd SKU / Carton Number[/TD]
[TD="bgcolor: #000000, align: center"]Carton[/TD]
[TD="bgcolor: #000000, align: center"]QTY 2[/TD]
[TD="bgcolor: #ff0000"][/TD]
[TD="bgcolor: #000000"]3rd SKU / Carton Number[/TD]
[TD="bgcolor: #000000, align: center"]Carton[/TD]
[TD="bgcolor: #000000, align: center"]QTY 3[/TD]
[TD="bgcolor: #ff0000"][/TD]
[TD="bgcolor: #000000"]4th SKU / Carton Number[/TD]
[TD="bgcolor: #000000, align: center"]Carton[/TD]
[TD="bgcolor: #000000, align: center"]QTY 4[/TD]
[TD="bgcolor: #ff0000"][/TD]
[TD="bgcolor: #000000"]5th SKU / Carton Number[/TD]
[TD="bgcolor: #000000, align: center"]Carton[/TD]
[TD="bgcolor: #000000, align: center"]QTY 5[/TD]
[TD="bgcolor: #ff0000"][/TD]
[TD="bgcolor: #000000"]6th SKU / Carton Number[/TD]
[TD="bgcolor: #000000, align: center"]Carton[/TD]
[TD="bgcolor: #000000, align: center"]QTY 6[/TD]

[TD="align: center"]CONTAINER - 01[/TD]

[TD="bgcolor: #b7e1cd, align: center"]1



[/TD]
[TD="align: center"]45[/TD]
[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="align: center"]CONTAINER - 02[/TD]

[TD="align: center"]46[/TD]
[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="align: center"]CONTAINER - 03[/TD]

[TD="align: center"]132[/TD]
[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="align: center"]CONTAINER - 04[/TD]

[TD="align: center"]1320[/TD]
[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="align: center"]CONTAINER - 05[/TD]

[TD="bgcolor: #b7e1cd, align: center"]1



[/TD]
[TD="align: center"]30[/TD]
[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #b7e1cd, align: center"]2



[/TD]
[TD="align: center"]29[/TD]
[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="align: center"]CONTAINER - 06[/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="align: center"]CONTAINER - 07[/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #ff0000"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="align: center"]CONTAINER - 08[/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="align: center"]CONTAINER - 09[/TD]

[TD="align: center"]1188[/TD]
[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="align: center"]CONTAINER - 10[/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="align: center"]CONTAINER - 11[/TD]

[TD="align: center"]32[/TD]
[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="align: center"]CONTAINER - 12[/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="align: center"]CONTAINER - 13[/TD]

[TD="align: center"]20[/TD]
[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="align: center"]CONTAINER - 14[/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="align: center"]CONTAINER - 15[/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="align: center"]CONTAINER - 16[/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="align: center"]CONTAINER - 17[/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="align: center"]CONTAINER - 18[/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="align: center"]CONTAINER - 19[/TD]

[TD="align: center"]32[/TD]
[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="align: center"]CONTAINER - 20[/TD]

[TD="bgcolor: #b7e1cd, align: center"]1



[/TD]
[TD="align: center"]3[/TD]
[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #b7e1cd, align: center"]1



[/TD]
[TD="align: center"]3[/TD]
[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #b7e1cd, align: center"]1



[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #b7e1cd, align: center"]2



[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #b7e1cd, align: center"]3



[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #ff0000"][/TD]

[TD="align: center"]CONTAINER - 21[/TD]

[TD="align: center"]330[/TD]
[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

[TD="bgcolor: #ff0000"][/TD]

</tbody>



2-475
24341
46-140
500S4
5050973705-14
510S4
53085
550
5520
6-563-2
6SILSP
7OB-HT
8MMSL

<colgroup><col style="width: 132px"><col width="73"><col width="107"><col width="66"><col width="120"><col width="120"><col width="120"><col width="120"><col width="120"><col width="120"><col width="120"></colgroup><tbody>
[TD="bgcolor: #000000"]Item[/TD]
[TD="bgcolor: #000000"]CARTON 1[/TD]
[TD="bgcolor: #000000"]CARTON 2[/TD]
[TD="bgcolor: #000000"]CARTON 3[/TD]
[TD="bgcolor: #000000"]CARTON 4[/TD]
[TD="bgcolor: #000000"]CARTON 5[/TD]
[TD="bgcolor: #000000"]CARTON 6[/TD]
[TD="bgcolor: #000000"]CARTON 7[/TD]
[TD="bgcolor: #000000"]CARTON 8[/TD]
[TD="bgcolor: #000000"]CARTON 9[/TD]
[TD="bgcolor: #000000"]CARTON 10[/TD]

</tbody>
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Sort by date Sort by votes
Hi,

Formula I have which works is:
============
=SUMIFS(wha1q,wha1s,$A3,wha1c,C$2)+SUMIFS(wha2q,wha2s,$A3,wha2c,C$2)+SUMIFS(wha3q,wha3s,$A3,wha3c,C$2)+SUMIFS(wha4q,wha4s,$A3,wha4c,C$2)+SUMIFS(wha5q,wha5s,$A3,wha5c,C$2)+SUMIFS(wha6q,wha6s,$A3,wha6c,C$2)+SUMIFS(wha7q,wha7s,$A3,wha7c,C$2)+SUMIFS(wha8q,wha8s,$A3,wha8c,C$2)+SUMIFS(wha9q,wha9s,$A3,wha9c,C$2)+SUMIFS(wha10q,wha10s,$A3,wha10c,C$2)+SUMIFS(conta1q,conta1s,$A3,conta1c,C$2)+SUMIFS(conta2q,conta2s,$A3,conta2c,C$2)+SUMIFS(conta3q,conta3s,$A3,conta3c,C$2)+SUMIFS(conta4q,conta4s,$A3,conta4c,C$2)+SUMIFS(conta5q,conta5s,$A3,conta5c,C$2)+SUMIFS(conta6q,conta6s,$A3,conta6c,C$2)+SUMIFS(conta7q,conta7s,$A3,conta7c,C$2)+SUMIFS(conta8q,conta8s,$A3,conta8c,C$2)+SUMIFS(conta9q,conta9s,$A3,conta9c,C$2)+SUMIFS(conta10q,conta10s,$A3,conta10c,C$2)
===========

It is ugly, and very slow to process.

Any suggestions to improve? Eg perhaps with a SUMIF - INDEX MATCH?

Tanks
Dave
 
Upvote 0
You must log in or register to reply here.

Similar threads

X
  • Question Question
How To Create Multiple Sheets(With Criteria) From One Sheet?
Replies
1
Views
306
Kerryx
K
mehidy1437
  • Question Question
Requesting for experts comments suggestion on my vba code
Replies
5
Views
877
mehidy1437
mehidy1437
E
  • Solved
Find a value matching a range upon several criteria
Replies
2
Views
156
ED38
E
M
  • Question Question
Help with FilterXML formula
Replies
0
Views
1K
montex01
M
T
  • Question Question
Dynamic Sheet Queries
Replies
1
Views
266
Vogateer
V

Forum statistics

Threads
1,223,236
Messages
6,170,915
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top