The sum of the pallet's weight  and of the carton's weight of the same shipment.

Occasional Contributor

Hi all,

How can I add the weight of different shipping types with the same reference number?

I have an excel line with information about each package. But I need sum of carton weight per shipment (customer order) and I need sum of pallet weight per shipment (customer order).
@Harun24HR Can you help me with this?
@Hans Vogelaar

Client order numberShip to CountryQuantityType of ShipWeight (KG)
1234NL1CARTON3.034
1234NL1CARTON2.31
1235DE1CARTON0.356
1236BE1CARTON0.25
1237LU1CARTON9.5
1235DE1CARTON1.545
1238AT1CARTON2.93
1236BE1CARTON1.205
1236BE1CARTON2.96
1239NL1CARTON1
1210FI2CARTON5
1237LU1CARTON6
1234NL1CARTON4.35
1234NL2CARTON6.53
1235DE1CARTON5.34
1236BE2CARTON8.99
1238AT1CARTON3.92
1236BE5CARTON5.02
1236BE1CARTON6.92
1239NL3CARTON23.65
1234NL1PALLET120.65
1234NL3PALLET300
1235DE1PALLET110.5
1236BE2PALLET299.87
1234NL1CARTON1.5
1234NL1PALLET200
1235DE1CARTON5.5
1236BE1CARTON6.7
1237LU1PALLET6
1210FI1PALLET250

 

Below the example of the final result information that i need : 
     
Client order numberShip to CountryQuantityType of ShipWeight (KG)
1234NL6CARTON17,724
1234NL5PALLET620.65
1235DE4CARTON12.741
1235DE1PALLET110.5

 

2 Replies

@JGA74 Wouldn't a pivot table do? See attached.

 

@JGA74
Look into the SUMIF function.
The SUMIF function will allow you to sum the values in a range that meet criteria.
For example you can write a formula like this:
If Client order number is in Column A, Ship to Country is in Column B, and the Quantity is in Column C.
=SUMIF(B:B,"NL",C:C) for NL country.

You can do the same for the weight column by changing the column from C to E.

SUMIF can also take string argumnets like this:
=SUMIF(C:C,">5") and that will give you all the > 5 values. Say you want to give a discount for shipping more than just 1.

You can also check the COUNTIF() and that works the same way but counts instead of adding.
You can also check the SUMIFS() function that uses multiple criteria.

I hope this helps!