work out cost of shipment

Copper Contributor

Which formula can I use in excel to work out the  cost of a shipment from this data




I want be able to type in total kilos in one cell and the answer would be for example £14.70 based on 43 kilos (20 at £6.99, 20 at £0.33ea and 3 at £0.37ea)


Any help most appreciated :)


3 Replies


Please use the solution suggested by the user below.

Boom, thanks very much!

@James_S1989 Although you already accepted an AI generated solution, the attached workbook contains an approach without the need for nested IF functions and hard coded boundaries for each of the tiers.