Total Cost Split Pro Rata

[ZeDoctor]

Hi guys,

Got a bit of a tricky one, been struggling with it for a while.

I've attached a sample workbook to show what i'm getting at..

In column A I have a booking reference that will often be duplicated up to a maximum of 15 times. In column D (CBM) there are a number of different cubi meatre measurements. What I need is for column F to find the sum of all CBM's for the booking reference in column A and then assign a percentage in column G for that line.

So for example, in row 5 booking ref A221-011857 has 11.25 assigned to it and the total sum of all A221-011857 CBMs would be 21.09 (Sum(D5:D10)) so this would be shown in Column F. Then in column G it would show 53.34% of that total CBM is allocated to row 5.

Apologies for the, probably, really drawn out way of explaining. I'd never make a teacher.

Thanks very much for any help in advance.

[Zygron]

Something like this?

Formula entered into F2 and G2 then copied/dragged down

Rows 2, 3 and 4 showing errors because there is no values in the CBM column
If you have a lot of these you can enter the G2 formula as
=IF(D2=0, "", D2/F2)

[Pete_UK]

Put this in F2 and copy down:

=SUMIF(A:A,A2,D:D)

Then put this in G2:

=IF(F2=0,0,D2/F2)

Format as a percentage, then copy down.

Hope this helps.

Pete

[ZeDoctor]

Zygron & Pete,

I knew it would be something like a simple formula.

A million thank you's though :D