Calculate Data based Matrix

[sunny81]

Dear Team,
Need help. I have around 500 cost centres. We maintain a matrix to allocate amounts from each cost centers to various brands based on a percentage set by management. Now I need to pull out cost for each brands for these cost centres based on the % matrix. Here is a sample data, please see if some of you could give some excel formula. I have around 200 brands for each scenarios like plan, forecast, monthly actuals etc.

Please see my sample data below:

Cost to Brand Percentage matrix:


[TABLE="class: grid, width: 230"]
<tbody>[TR]
[TD]Cost Centre (CC)[/TD]
[TD]Brand 1[/TD]
[TD]Brand 2[/TD]
[/TR]
[TR]
[TD]CC1[/TD]
[TD]50%[/TD]
[TD]50%[/TD]
[/TR]
[TR]
[TD]CC1[/TD]
[TD]80%[/TD]
[TD]20%[/TD]
[/TR]
[TR]
[TD]CC2[/TD]
[TD]70%[/TD]
[TD]30%[/TD]
[/TR]
[TR]
[TD]CC3[/TD]
[TD]55%[/TD]
[TD]45%[/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]


Raw Data:
[TABLE="class: grid, width: 143"]
<tbody>[TR]
[TD]Cost Centre (CC)[/TD]
[TD]£[/TD]
[/TR]
[TR]
[TD]CC1[/TD]
[TD="align: right"]1000[/TD]
[/TR]
[TR]
[TD]CC1[/TD]
[TD="align: right"]2000[/TD]
[/TR]
[TR]
[TD]CC2[/TD]
[TD="align: right"]3200[/TD]
[/TR]
[TR]
[TD]CC3[/TD]
[TD="align: right"]4000[/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]

Required Total cost for each brand

Brand 1 = ?
Brand 2 =?

 

[BigDat6CC]

Hello sunny

Because you show CC1 twice in each matrix I have trouble understanding the relationship between the two matrices. Please will you give some guidance

 

[sunny81]

Hi - So sorry about the confusion. It was a typo to have CC1 repeated twice. It should be like this
[TABLE="class: cms_table_grid, width: 230"]
<tbody>[TR]
[TD]Centre (CC)[/TD]
[TD]Brand 1[/TD]
[TD]Brand 2[/TD]
[/TR]
[TR]
[TD]CC1[/TD]
[TD]50%[/TD]
[TD]50%[/TD]
[/TR]
[TR]
[TD]CC2[/TD]
[TD]80%[/TD]
[TD]20%[/TD]
[/TR]
[TR]
[TD]CC3[/TD]
[TD]70%[/TD]
[TD]30%[/TD]
[/TR]
[TR]
[TD]CC4[/TD]
[TD]55%[/TD]
[TD]45%[/TD]
[/TR]
</tbody>[/TABLE]

 

[SyedUsman]

=SUMPRODUCT(B2:B5,F2:F5)

=SUMPRODUCT(C2:C5,F2:F5)

assuming B2:B5 is brand 1 percentages & F2:F5 is Cost centers cost to allocate for brand 1
assuming C2:C5 is brand 2 percentages & F2:F5 is Cost centers cost to allocate for brand 1

CHEERS