How to split a total sum using individual costs from two reference values

Hi Excel Forum,

I am hoping someone can assist me.

I am trying to split the total sum (Cell J13) proportionally according to the % of the individual costs (column E) & also the Duty % (Column H).

I have tried to do this by using the % value (column F) and it will work providing the % values are all the same in (column H)

The problem is when the % values are not the same in (column H)

I have tried to use the % values (column M) from the data in (Column F & I) the total sum is correct but the duty cost % split per item is not correct.

Could some one assist in how I can factor in the Duty % (Column H) to the formula in (column K) or what needs to be changed in the formula in (column M) in order to split the costs proportionally to each item according to the item value & duty %.

I have attached the file which is only currently using the Total price % split (column F) to calculate the split.

Apologizes in advance if I've not done something correctly on this post as this is my first post.

Thank you in advance.

Ian

H11 is not clear.

Hi Tim,

H11 is a sum of the individual % values in column H. I thought that this value would have to be used to work out the individual % split for each item in column I.

is % of duty the same for all items?

No the % of duty can vary for this example the item on row 6 has a duty of 30%. The rest have a duty of 10%

If all duty was the same I could just use the % split from the cost of each item column F. I have shown in Colum K

But due to the % of duty varying I need to use both the % from the cost of each item and also the duty % of each item.

That is what I need to work out how to do.

O2=N13/SUMPRODUCT(F4:F9,H4:H9)
=$O$2*F4*H4 and pull down

That is what I was trying to do.
Thank you very much for your time & support on this Tim

you are welcome.