get total weight of items by multiplying different cells by different values

[smfenn]

I'm trying to calculate the total weight of an order by multiplying our product columns by different amounts:

Cells F:L + AG:AJ need to be multiplied by 7.2
Cells M,O,Q,S,U,W,Y,AA need to to be multiplied by 3.3
Calls, N, P, R, T, V, X, Z, AB need to be multiplied by 6.6
Cells AC + AE multiplied by 10.8
Cells AF + AD need to be multiplied by 8.4

This then needs to show a total in cell AT -I'm not 100% sure this is possible. Not sure if it's possible to make this work with an ARRAY formula or needs something more complex. Would welcome any help or support folks might be able to provide.

 

[Dave Patton]

What have your tried?
What rows are used?
Can you post an extract of a relevant example say with 10 rows and the expected result.
You can post the example with the forum's tool call XL2BB.

 

[smfenn]

So far I've only tried '=SUM{E36:K36*7.2*L36,N36,P36,R36,T36,V36,X36,Z36*3.3*M36,O36,Q36,S36,U36,W36,Y36,AA36*6.6*AB36:AI36*10.8}

I would want it to sum every row that has an order placed, but not every column would be used.

Here's an example row based on a real row from our spread sheet

 

[Dave Patton]

Your post did not show information.
I just noticed that you use a Mac ; I do not know if the Add-in XL2BB works with your software.

We are not paid support people; we are volunteers who try to help people.
To guess at your challenges, may or may not be useful.

What parts of your formula actually work? Try Excel's Formula Evaluate Formula.

Does the following help?

T202205a.xlsm
	E	F	G	H	I	J	K	L	M	N	O	P
31	113.4
32
33
34
35
36	1	2	3				7			2		4
1a

Cell Formulas
Range	Formula
E31	E31	=SUM(E36:K36)*7.2+SUM(N36,P36)*3.3

 

[smfenn]

David,

Didn't realise you could +SUM formulas - so that has helped, thank you so much.

 

[Dave Patton]

Thanks for the feedback. Dave

 

[Dave Patton]

If you had posted an example that showed how the rates are identified, we could have provided a better answer.
You can review and try each of the following

T202205a.xlsm
	E	F	G	H	I	J	K	L	M	N	O	P
31	113.4	113.4	113.4	113.4
32
33
34	7.2	7.2	7.2	7.2	7.2	7.2	7.2			3.3		3.3
35
36	1	2	3				7			2		4
1a

Cell Formulas
Range	Formula
E31	E31	=SUM(E36:K36)*7.2+SUM(N36,P36)*3.3
F31	F31	=SUM(E36:K36*7.2,N36:P36*3.3)
G31	G31	=SUM(E36:K36)*7.2+SUM(L36,N36,P36,R36,T36,V36,X36,Z36)*3.3+SUM(M36,O36,Q36,S36,U36,W36,Y36,AA36)*6.6+SUM(AB36:AI36)*10.8
H31	H31	=SUMPRODUCT(E34:P34,E36:P36)