Adding lots of numbers together...

[rabbits]

I'm not sure the title does it justice, but I'm trying to find a way of working out the weights of around 300 different boxes. Each box has several versions of about 15 different items, each weighing somewhere from a few grams to over a kilo (so it might be 7 copies of item X weighing 3 grams etc etc). Each box contains different items and therefore has a different weight. Oh, and the items (i.e. weight) changes every week.

At the moment I'm adding it all together manually, but it's taking forever. Could there be a way of automating / speeding it up? Suggestions very welcome!

 

[bebo021999]

It may helps a lot, if you could provide an image/screenshot, or mini sheet

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[rabbits]

Book3
	A	B	C	D	E	F	G	H	I	J	K
1		Quantity Item 1	Quantity Item 2	Quantity Item 3	Quantity Item 4	Quantity Item 5	Box Weight (kg)			Item	Weight (grams)
2	Store A	3	5	5	2	0	0.053			Item 1	2
3	Store B	4	7	3	6	8	7.248			Item 2	6
4	Store C	6	10	7	4	3	8.428			Item 3	3
5	Store D	0	3	10	6	5	3.6			Item 4	1
6	Store E	1	1	0	5	3	1.2			Item 5	1200
Sheet1

Cell Formulas
Range	Formula
G2:G6	G2	=((B2*K2)+(C2*K3)+(D2*K4)+(E2*K5)+(F2*K6))/1000

 

[rabbits]

Sorry, hope that helps (this is just my toy, so there would be maybe around 20 items total and 300ish stores

 

[Alex Blakenburg]

That layout doesn't really work if you have a variable number of items across the page.
Is that really how the data arrives when you get it ?
Also will you have that look up table in columns J & K for all items ?

I suspect you will better off using Power Query.
You can use VLookup or Xlookup to do what you have in your sample data, but that method will struggle if you have a variable number of columns.

 

[rabbits]

That layout doesn't really work if you have a variable number of items across the page.
Is that really how the data arrives when you get it ?
Also will you have that look up table in columns J & K for all items ?

I suspect you will better off using Power Query.
You can use VLookup or Xlookup to do what you have in your sample data, but that method will struggle if you have a variable number of columns.

It's how I've laid it out, but happy to have suggestions of how to lay it out better.

I feared someone might mention power query - I don't suppose you have any suggestions for a good starting point? I've never really used it before

 

[Alex Blakenburg]

Can you show us the data in the format that you first get it ?

 

[rabbits]

Can you show us the data in the format that you first get it ?

I'm not sure how I'd do it to be honest - all the data comes from several different locations, I've just pulled it together using a mix of copy / paste and vlookups

 

[Alex Blakenburg]

Ouch. That sounds very high maintenance.
In the format you are showing us is the Box Weight column going to keep moving ?
Also are they you're real column names, if not what are the real names.

Is your lookup table really on Sheet1 or is it on another sheet, if so what is its name ?

 

[Alex Blakenburg]

I had another look at you example and your formulas are only correct for the first row.
You didn't fix the row in your lookup table so the price for each item moved down the table.

Here is an example that relies on you current Heading Naming convention (I suspect you have oversimplified the headings)

20220906 Calculate Total using Lookup possibly PQ rabbits.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1		Quantity Item 1	Quantity Item 2	Quantity Item 3	Quantity Item 4	Quantity Item 5	Box Weight (kg)			Item	Weight (grams)
2	Store A	3	5	5	2	0	0.053			Item 1	2
3	Store B	4	7	3	6	8	9.665			Item 2	6
4	Store C	6	10	7	4	3	3.697			Item 3	3
5	Store D	0	3	10	6	5	6.054			Item 4	1
6	Store E	1	1	0	5	3	3.613			Item 5	1200
Test

Cell Formulas
Range	Formula
G2:G6	G2	=($B2*VLOOKUP(SUBSTITUTE(B$1,"Quantity ",""),$J$2:$K$6,2,FALSE) +$C2*VLOOKUP(SUBSTITUTE(C$1,"Quantity ",""),$J$2:$K$6,2,FALSE) +$D2*VLOOKUP(SUBSTITUTE(D$1,"Quantity ",""),$J$2:$K$6,2,FALSE) +$E2*VLOOKUP(SUBSTITUTE(E$1,"Quantity ",""),$J$2:$K$6,2,FALSE) +$F2*VLOOKUP(SUBSTITUTE(F$1,"Quantity ",""),$J$2:$K$6,2,FALSE))/1000