Create an array of summed columns

[Jek61]

Hi,

nice to be part of the forum.

I am finding it difficult to find a solution to the following and would welcome any assistance.

I have created a table which expands as new data is added, each column is a unique item which has a weight. I need to obtain the gross weight over all the columns, this will vary as the table expands.

Idealy the sumproduct function would work by multiplying the sum of each column by the weight of the item, but how can I create a formula which will create an array of the "sum" of each column then use this information as an array in the sumproduct function calulation then combine this with the weight array

I can get the result by creating a total on row 1, which sums each column the uses each total in the sumproduct function but I am interested if there is a more efficient way to achieve this answer.

Many thanks for looking.

Jek61

 

[jasonb75]

I think that this will do what you need. You will need to change the 4 names of 'Table3' in the formula to the correct name for your own table.

=SUMPRODUCT(INDEX(Table3,0,2):INDEX(Table3,0,COLUMNS(Table3))*B2:INDEX(2:2,COLUMNS(Table3)))/1000

 

[Jek61]

Hi Jasonb75,

worked great many thanks.

Jek61.

 

[Peter_SSs]

Welcome to the MrExcel board!

What about this? Might need Ctrl+Shift+Enter confirmation with your Excel 2016 (& 635 if you do not have the new dynamic array functionality)

20 07 30.xlsm
	A	B	C	D	E
1
2	Weight (kg)	21.3	17.3	13.3
3	Date1	Item 1	Item 2	Item 3
4	18/07/1961	0	0	0
5	30/07/2020	20	12	13
6	30/07/2020	5	5	5
7
8
9	1.066
10
Jek61

Cell Formulas
Range	Formula
A9	A9	=SUM(IFERROR(Table3*(2:2),0))/1000

.. or if you know that your table would never exceed, say, 100 columns you could reduce the calculation overhead somewhat with

=SUM(IFERROR(Table3*(A2:CV2),0))/1000

 

[Peter_SSs]

A further option

Enter a zero in A2 and custom format that cell as "Weight (kg)" then try this formula

=SUM(Table3*OFFSET(Table3,-2,,1))/1000

 

[Jek61]

Hi,

many thanks this solution also works.

Regards

Jek61

 

[Jek61]

Welcome to the MrExcel board!

What about this? Might need Ctrl+Shift+Enter confirmation with your Excel 2016 (& 635 if you do not have the new dynamic array functionality)

20 07 30.xlsm
	A	B	C	D	E
1
2	Weight (kg)	21.3	17.3	13.3
3	Date1	Item 1	Item 2	Item 3
4	18/07/1961	0	0	0
5	30/07/2020	20	12	13
6	30/07/2020	5	5	5
7
8
9	1.066
10
Jek61

Cell Formulas
Range	Formula
A9	A9	=SUM(IFERROR(Table3*(2:2),0))/1000

.. or if you know that your table would never exceed, say, 100 columns you could reduce the calculation overhead somewhat with

=SUM(IFERROR(Table3*(A2:CV2),0))/1000

Hi The solution works fine.

many thanks.

 

[Peter_SSs]

You're welcome. Thanks for the confirmation.