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

[sheeeets]

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

LOCATION ID

1st SKU / Carton Number

Carton

QTY 1

2nd SKU / Carton Number

Carton

QTY 2

3rd SKU / Carton Number

Carton

QTY 3

4th SKU / Carton Number

Carton

QTY 4

5th SKU / Carton Number

Carton

QTY 5

6th SKU / Carton Number

Carton

QTY 6

CONTAINER - 01

F-SM

1

45

CONTAINER - 02

F-FID

46

CONTAINER - 03

VF-RFT15-BF

132

CONTAINER - 04

VF-RFT15-GF

1320

CONTAINER - 05

F-SQUAT

1

30

F-SQUAT

2

29

CONTAINER - 06

CONTAINER - 07

LFAT3SG

11

LFPACFIB

2

CONTAINER - 08

CONTAINER - 09

VF-RFT15-RF

1188

CONTAINER - 10

CONTAINER - 11

V-V1200

32

CONTAINER - 12

CONTAINER - 13

V-V1000

20

CONTAINER - 14

CONTAINER - 15

AS-AssaultB

14

CONTAINER - 16

CONTAINER - 17

GSLED

17

CONTAINER - 18

CONTAINER - 19

V-V1000

32

CONTAINER - 20

LFPHCT

1

3

LFPHCT

1

3

LFPAOIB

1

1

LFPAOIB

2

1

LFPAOIB

3

1

CONTAINER - 21

VF-RFT15-RF

330

<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>
</tbody>

Item	CARTON 1	CARTON 2	CARTON 3	CARTON 4	CARTON 5	CARTON 6	CARTON 7	CARTON 8	CARTON 9	CARTON 10
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>
</tbody>

 

[sheeeets]

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

 

[sheeeets]

Is this wrong area to post these questions?

Apologies if it is.

Thanks