Usage Allocation Formula

[Bkisley]

Hello everyone!


Tab 1 called "Component Matrix"
I have a list of all my components vertically in Column C --> C5:C11
I have a list of all my programs in column F4:J4
See below for what I mean - This table represents a usage matrix.
Program 2 has 1 of each of the following components - 1, 3,4,5 and 7

Component Name	Program 1	Program 2	Program 3	Program 4	Program 5
Component 1		1
Component 2	1		1		1
Component 3	1	1	1	1
Component 4	1	1
Component 5		1	1	1
Component 6			1		1
Component 7		1

<tbody>
</tbody>

Tab 2 called "Allocation"
Here is a sample equation
Program 2 component 4 = 50,000* (100,000*(50,000+100000)
Take the tooling and multiply it against that program's volume divided by the total volume of that component

	Lifetime Volume -->	50,000	100,000	150,000	200,000	250,000
Component Name	Component Tooling	Program 1	Program 2	Program 3	Program 4	Program 5
Component 1	10,000		10,000
Component 2	20,000	2,222		6,667		11,111
Component 3	30,000	3,000	6,000	9,000	12,000
Component 4	40,000	13,333	16,667
Component 5	50,000		11,111	16,667	22,222
Component 6	60,000			22,500		37,500
Component 7	70,000		70,000

<tbody>
</tbody>

I need an equation that first does the second table and then sums up all the program 1 costs. Same for program 2,3,.....

Is this possible!?