Forum Discussion
The sum of the pallet's weight and of the carton's weight of the same shipment.
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 number | Ship to Country | Quantity | Type of Ship | Weight (KG) |
| 1234 | NL | 1 | CARTON | 3.034 |
| 1234 | NL | 1 | CARTON | 2.31 |
| 1235 | DE | 1 | CARTON | 0.356 |
| 1236 | BE | 1 | CARTON | 0.25 |
| 1237 | LU | 1 | CARTON | 9.5 |
| 1235 | DE | 1 | CARTON | 1.545 |
| 1238 | AT | 1 | CARTON | 2.93 |
| 1236 | BE | 1 | CARTON | 1.205 |
| 1236 | BE | 1 | CARTON | 2.96 |
| 1239 | NL | 1 | CARTON | 1 |
| 1210 | FI | 2 | CARTON | 5 |
| 1237 | LU | 1 | CARTON | 6 |
| 1234 | NL | 1 | CARTON | 4.35 |
| 1234 | NL | 2 | CARTON | 6.53 |
| 1235 | DE | 1 | CARTON | 5.34 |
| 1236 | BE | 2 | CARTON | 8.99 |
| 1238 | AT | 1 | CARTON | 3.92 |
| 1236 | BE | 5 | CARTON | 5.02 |
| 1236 | BE | 1 | CARTON | 6.92 |
| 1239 | NL | 3 | CARTON | 23.65 |
| 1234 | NL | 1 | PALLET | 120.65 |
| 1234 | NL | 3 | PALLET | 300 |
| 1235 | DE | 1 | PALLET | 110.5 |
| 1236 | BE | 2 | PALLET | 299.87 |
| 1234 | NL | 1 | CARTON | 1.5 |
| 1234 | NL | 1 | PALLET | 200 |
| 1235 | DE | 1 | CARTON | 5.5 |
| 1236 | BE | 1 | CARTON | 6.7 |
| 1237 | LU | 1 | PALLET | 6 |
| 1210 | FI | 1 | PALLET | 250 |
| Below the example of the final result information that i need : | ||||
| Client order number | Ship to Country | Quantity | Type of Ship | Weight (KG) |
| 1234 | NL | 6 | CARTON | 17,724 |
| 1234 | NL | 5 | PALLET | 620.65 |
| 1235 | DE | 4 | CARTON | 12.741 |
| 1235 | DE | 1 | PALLET | 110.5 |
- 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! JGA74 Wouldn't a pivot table do? See attached.
