Confused...

  • Thread starter Thread starter LdoubleE80
  • Start date Start date
L

LdoubleE80

Our order entry system spits out reports to excel. One of the hassels
is freight dollars on shipments with multiple items. It shows every
item as having the total freight rather than dividing it out equally
among every item. The report looks like this.

Column A Item QTY FRT
Order 123 x 240 120.00
Order 123 y 120 120.00
Order 124 x 400 140.00
Order 124 z 200 140.00


I want a formula to figure out the frieght for each item based on the
order #, so for Order 123, Item "x" would have $80 of freight, while
Item "y" would have $40 of freight. What function do I use here?
 
Hi

Try this for size!!
=C2/SUMIF($A$2:$A$5,A2,$C$2:$C$5)*D2
assuming your data starts in A2 and goes to D5, for example. Copy this into
E2 and fill down.

Hope this helps.
 
Try this in E2 and copy down

=ROUND(D2*C2/SUMIF(A:A,A2,C:C),2)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
I would add the total qty figures (360), divide the item
(x) qty (240) by that total to get the % (66.7%) and
multiply that by total frt. (qty/(sum(qty1:qtyN))*frt or,
in the first case: (240/(sum(240,120))*120.
 
Back
Top