Multiple Array Sum

[Silvo]

I am looking for a way to add multiple arrays of a consistent row and column size, based on varying criteria that controls the row and columns forming the array. I'm trying to distribute some costs over different unit types and tenures based on 3 criteria that generate the arrays.

This is an example of the sort of data I want to manipulate:

Cost	Value	Tenure	Type	Multiple
Item_1	558,700	C1_P1	R1_P1	R2_P2
Item_2	886,100	C1_P2	R1_P4	R2_P4
Item_3	619,300	C1_P2	R1_P5	R2_P5
Item_4	883,100	C1_P5	R1_P7	R2_P1
Item_5	204,800	C1_P4	R1_P2	R2_P2
Item_6	847,200	C1_P5	R1_P6	R2_P5
Item_7	941,200	C1_P7	R1_P2	R2_P2
Item_8	555,400	C1_P1	R1_P2	R2_P3
Item_9	723,800	C1_P4	R1_P7	R2_P2
Item_10	416,300	C1_P4	R1_P7	R2_P3
Item_11	228,800	C1_P1	R1_P1	R2_P1
Item_12	746,000	C1_P3	R1_P2	R2_P3
Item_13	427,300	C1_P1	R1_P3	R2_P4
Item_14	845,700	C1_P4	R1_P1	R2_P4
Item_15	230,800	C1_P7	R1_P6	R2_P4
Item_16	613,400	C1_P1	R1_P1	R2_P1
Item_17	482,800	C1_P1	R1_P6	R2_P1
Item_18	19,600	C1_P2	R1_P2	R2_P2
Item_19	504,300	C1_P7	R1_P7	R2_P4
	10,734,600

These are the profiles I'm controlling in 3 columns above:

PROFILES
Tenure
Ref	V	W	X	Y	Z
C1_P1	1	1	1	1	1
C1_P2	1	1
C1_P3			1	1	1
C1_P4	1
C1_P5		1
C1_P6			1
C1_P7				1
C1_P8					1
Type
Ref	A	B	C	D	E	F	G	H	I
R1_P1	1	1	1	1	1	1	1	1	1
R1_P2	1	1
R1_P3			1	1	1
R1_P4						1	1
R1_P5								1	1
R1_P6	1		1	1	1		1	1
R1_P7		1				1			1
Multiple
Ref	A	B	C	D	E	F	G	H	I
R2_P1	1	1	1	1	1	1	1	1	1
R2_P2	45	48	65	68	72	85	86	92	103
R2_P3	145,500	156,000	204,000	212,000	223,000	256,000	257,500	267,500	288,000
R2_P4	31,500	32,600	42,300	43,500	45,400	53,100	52,900	55,200	59,200
R2_P5	2	2	3	3	4	5	5	7	8

Then the above profiles are used to multiply against the quantities below:

QUANTITY
	Tenure
Type	V	W	X	Y	Z
A	8	0	8	8	4
B	3	1	2	3	6
C	6	8	4	4	4
D	0	7	2	9	4
E	2	3	3	6	9
F	3	3	4	5	4
G	6	3	4	3	4
H	6	4	6	5	1
I	2	1	6	6	1

I can get it to work by using a helper sheet to expand the arrays (Quantity x Tenure x Type x Multiple) into a percentage and then multiply by Value and add back together. This works fine, except I need to do it hundreds of times and then it becomes difficult to manage.

Item_1	360	0	360	360	180		2.6%	0.0%	2.6%	2.6%	1.3%		14,544	0	14,544	14,544	7,272
	144	48	96	144	288		1.0%	0.3%	0.7%	1.0%	2.1%		5,818	1,939	3,878	5,818	11,635
	390	520	260	260	260		2.8%	3.8%	1.9%	1.9%	1.9%		15,756	21,008	10,504	10,504	10,504
	0	476	136	612	272		0.0%	3.4%	1.0%	4.4%	2.0%		0	19,231	5,494	24,725	10,989
	144	216	216	432	648		1.0%	1.6%	1.6%	3.1%	4.7%		5,818	8,727	8,727	17,453	26,180
	255	255	340	425	340		1.8%	1.8%	2.5%	3.1%	2.5%		10,302	10,302	13,736	17,170	13,736
	516	258	344	258	344		3.7%	1.9%	2.5%	1.9%	2.5%		20,847	10,423	13,898	10,423	13,898
	552	368	552	460	92		4.0%	2.7%	4.0%	3.3%	0.7%		22,301	14,867	22,301	18,584	3,717
	206	103	618	618	103		1.5%	0.7%	4.5%	4.5%	0.7%		8,323	4,161	24,968	24,968	4,161
Item_2	0	0	0	0	0		0.0%	0.0%	0.0%	0.0%	0.0%		0	0	0	0	0
	0	0	0	0	0		0.0%	0.0%	0.0%	0.0%	0.0%		0	0	0	0	0
	0	0	0	0	0		0.0%	0.0%	0.0%	0.0%	0.0%		0	0	0	0	0
	0	0	0	0	0		0.0%	0.0%	0.0%	0.0%	0.0%		0	0	0	0	0
	0	0	0	0	0		0.0%	0.0%	0.0%	0.0%	0.0%		0	0	0	0	0
	159300	159300	0	0	0		20.0%	20.0%	0.0%	0.0%	0.0%		177,621	177,621	0	0	0
	317400	158700	0	0	0		39.9%	20.0%	0.0%	0.0%	0.0%		353,905	176,952	0	0	0
	0	0	0	0	0		0.0%	0.0%	0.0%	0.0%	0.0%		0	0	0	0	0
	0	0	0	0	0		0.0%	0.0%	0.0%	0.0%	0.0%		0	0	0	0	0
Item_3	0	0	0	0	0		0.0%	0.0%	0.0%	0.0%	0.0%		0	0	0	0	0
	0	0	0	0	0		0.0%	0.0%	0.0%	0.0%	0.0%		0	0	0	0	0
	0	0	0	0	0		0.0%	0.0%	0.0%	0.0%	0.0%		0	0	0	0	0
	0	0	0	0	0		0.0%	0.0%	0.0%	0.0%	0.0%		0	0	0	0	0
	0	0	0	0	0		0.0%	0.0%	0.0%	0.0%	0.0%		0	0	0	0	0
	0	0	0	0	0		0.0%	0.0%	0.0%	0.0%	0.0%		0	0	0	0	0
	0	0	0	0	0		0.0%	0.0%	0.0%	0.0%	0.0%		0	0	0	0	0
	42	28	0	0	0		44.7%	29.8%	0.0%	0.0%	0.0%		276,709	184,472	0	0	0
	16	8	0	0	0		17.0%	8.5%	0.0%	0.0%	0.0%		105,413	52,706	0	0	0
Item_4	0	0	0	0	0		0.0%	0.0%	0.0%	0.0%	0.0%		0	0	0	0	0
	0	1	0	0	0		0.0%	20.0%	0.0%	0.0%	0.0%		0	176,620	0	0	0
	0	0	0	0	0		0.0%	0.0%	0.0%	0.0%	0.0%		0	0	0	0	0
	0	0	0	0	0		0.0%	0.0%	0.0%	0.0%	0.0%		0	0	0	0	0
	0	0	0	0	0		0.0%	0.0%	0.0%	0.0%	0.0%		0	0	0	0	0
	0	3	0	0	0		0.0%	60.0%	0.0%	0.0%	0.0%		0	529,860	0	0	0
	0	0	0	0	0		0.0%	0.0%	0.0%	0.0%	0.0%		0	0	0	0	0
	0	0	0	0	0		0.0%	0.0%	0.0%	0.0%	0.0%		0	0	0	0	0
	0	1	0	0	0		0.0%	20.0%	0.0%	0.0%	0.0%		0	176,620	0	0	0
Item_5	360	0	0	0	0		71.4%	0.0%	0.0%	0.0%	0.0%		146,286	0	0	0	0
	144	0	0	0	0		28.6%	0.0%	0.0%	0.0%	0.0%		58,514	0	0	0	0
	0	0	0	0	0		0.0%	0.0%	0.0%	0.0%	0.0%		0	0	0	0	0
	0	0	0	0	0		0.0%	0.0%	0.0%	0.0%	0.0%		0	0	0	0	0
	0	0	0	0	0		0.0%	0.0%	0.0%	0.0%	0.0%		0	0	0	0	0
	0	0	0	0	0		0.0%	0.0%	0.0%	0.0%	0.0%		0	0	0	0	0
	0	0	0	0	0		0.0%	0.0%	0.0%	0.0%	0.0%		0	0	0	0	0
	0	0	0	0	0		0.0%	0.0%	0.0%	0.0%	0.0%		0	0	0	0	0
	0	0	0	0	0		0.0%	0.0%	0.0%	0.0%	0.0%		0	0	0	0	0
Item_6	0	0	0	0	0		0.0%	0.0%	0.0%	0.0%	0.0%		0	0	0	0	0
	0	0	0	0	0		0.0%	0.0%	0.0%	0.0%	0.0%		0	0	0	0	0
	0	24	0	0	0		0.0%	24.0%	0.0%	0.0%	0.0%		0	203,328	0	0	0
	0	21	0	0	0		0.0%	21.0%	0.0%	0.0%	0.0%		0	177,912	0	0	0
	0	12	0	0	0		0.0%	12.0%	0.0%	0.0%	0.0%		0	101,664	0	0	0
	0	0	0	0	0		0.0%	0.0%	0.0%	0.0%	0.0%		0	0	0	0	0
	0	15	0	0	0		0.0%	15.0%	0.0%	0.0%	0.0%		0	127,080	0	0	0
	0	28	0	0	0		0.0%	28.0%	0.0%	0.0%	0.0%		0	237,216	0	0	0
	0	0	0	0	0		0.0%	0.0%	0.0%	0.0%	0.0%		0	0	0	0	0

This is the end result I'd like without the helper sheet I'm using above:

	Tenure
Type	V	W	X	Y	Z	Total
A	468,570	0	365,715	1,076,270	182,857	2,093,412
B	454,029	198,181	90,028	472,600	270,084	1,484,921
C	230,165	333,634	65,153	90,848	65,153	784,954
D	0	293,936	33,150	208,627	66,299	602,012
E	81,377	152,659	50,995	143,357	152,984	581,371
F	765,794	731,012	31,374	225,566	31,374	1,785,120
G	587,376	337,956	45,232	58,024	45,232	1,073,820
H	518,835	467,890	69,302	99,666	11,550	1,167,243
I	563,121	237,897	51,424	300,732	8,571	1,161,745
Total	3,669,268	2,753,165	802,373	2,675,690	834,104	10,734,600

I can get it to work with the BYCOL or BYROW formulas but I want to be able to further manipulate the data for different scenarios later and I can’t if it’s already been put into totals.

I feel like it should be possible without the helper sheet but I am struggling and was hoping someone might be able to help or point me in the right direction. Not sure this is an easy one for people with VBA skills that I lack.

Really hope that makes sense and appreciate and help offered.

 

[Silvo]

Is anyone able to help me with this please? I'd really like to know if there's a way to do it and it's been driving me mad for a few weeks ?

Do I need to present the above in a different format (I'd not used the XL2BB before) or do I need to expand on anything.

Do I just need to work it by either row or column or on a cell basis rather than trying to manage it in a single array?

Thanks