How to calculate cost per unit in Excel

Cost per unit for one purchase

The sheet is a small online shop's packaging purchases: the item, the number of units, the invoice total and the freight charged on each order. Column F holds =IF(C2>0,D2/C2,"") for every row. The IF stops a #DIV/0! on PO-1230, where the label rolls were received but the units have not been entered yet.

Format the unit cost with more decimals than you would use for a price. Packaging, ingredients and parts often cost fractions of a cent, and a 2-decimal format would show 0.43 here and hide the real number.

Price per pack: convert to the unit you count

Suppliers often price per pack, case or roll, while you use single items. Divide the pack price by the number of items in the pack. A roll of 500 shipping labels for $18.90 is 0.0378 per label.

Decide on one unit and keep to it. If a recipe uses grams and the supplier sells by the kilo, or you sell single boxes but buy in bundles of 25, convert in a clearly labelled cell like I2 instead of typing the pack size inside a formula. Mixing per-pack and per-item costs in one column is the most common reason a margin looks far too good or far too bad.

Landed cost: include freight

What a unit really costs you includes getting it to you. Add the freight to the invoice before dividing: (D2+E2)/C2. On PO-1180 that moves the box from 0.425 to 0.461, an 8% difference, which matters when you set prices from it.

Freight on the invoice is the simple case. If one shipping charge covers several different items, split it by units (each item's units / total units * freight), or by value or weight if the items differ a lot in size. Write the rule down in a note next to the cell, because whoever updates the sheet next will have to follow it. Duties, payment fees and packaging can be added the same way.

Blended cost across several orders

You bought mailer boxes three times at different prices: 0.425, 0.3875 and 0.47 per box. The tempting answer is the average of the three, 0.4275. It is wrong, because it gives the 300-box order the same say as the 1,200-box order. The true cost of the 2,000 boxes you bought is all the money spent divided by all the boxes: 818.50 / 2,000 = 0.40925.

SUMIFS does it for one item in one formula, with the item name in I3. Add the freight SUMIFS to the top line for the blended landed cost: =(SUMIFS(D2:D8,B2:B8,I3)+SUMIFS(E2:E8,B2:B8,I3))/SUMIFS(C2:C8,B2:B8,I3) returns 0.43775.

Never divide all the cost by all the units across different items. =SUM(D2:D7)/SUM(C2:C7) returns about 0.2076, a number that is not the cost of anything, because tissue sheets at under 5 cents drag it down.

When you only have unit prices: SUMPRODUCT

Supplier price lists and some exports give you a unit price and a quantity, not an invoice total. Then weight each price by its quantity: SUMPRODUCT multiplies units by unit cost row by row and adds the results, and you divide by the total units. It gives the same 0.40925 as the SUMIFS version.

Write the condition as a separate argument with --, as below. The shorter form =SUMPRODUCT((B2:B8=I3)*C2:C8*F2:F8) returns #VALUE!, because multiplying with * fails on the empty text in F8. Comma-separated arrays treat text as 0.

Rounding to cents: when to do it and when not to

Keep unit costs unrounded while you calculate. The labels cost 0.0378 each. =ROUND(I1/I2,2) gives 0.04, and 500 labels at 0.04 come to $20.00, not the $18.90 you paid: a 6% error from one rounding. Even at larger amounts it adds up: PO-1194's 0.3875 rounds to 0.39, and 1,200 boxes at 0.39 is $468 against an invoice of $465.

Round only where a cent amount is final: a selling price, an invoice line, a figure in a report. Use ROUND(...,4) or more for unit costs you will multiply again, and ROUNDUP(...,2) when you set a price from a cost and never want to round below it.

Which formula to pick

One purchase: total cost / units, plus freight on top if it was charged. A pack price: pack price / items per pack. Several purchases of the same item: SUMIFS of cost / SUMIFS of units, or SUMPRODUCT when you only have unit prices. Never AVERAGE a column of unit costs, and keep the decimals until the final price. From the cost per unit, the markup and profit margin guides take you to a selling price.