Level 2 · Measure the return rate in Excel

What the sheet holds

Priya’s export has one row for every category and order status, 24 rows in all, built from order lines with a quantity above zero:

column	holds
A, B	category and order status
C	order lines sold
D	of those, lines with a return
E	net sales: quantity × price × (1 − discount)
F	refunds paid on those lines

The two small tables at H1 and H7 are yours to fill.

Step 1: check before you compare

Before computing a product return rate, see how returns sit across order statuses. SUMIF adds up one column where another column matches a value:

=SUMIF($B$2:$B$25,H2,$C$2:$C$25)

reads “add up C where B equals the status in H2”. Lock the data ranges with $ and leave H2 free, so the same formula fills I2:I5. The rate in K is returned divided by lines.

The result is the reason for every later choice: refunded orders return almost all their lines, because the whole order goes back, while completed orders return a few percent. Cancelled and pending orders never shipped. A product return rate therefore uses completed orders only, and the refunded orders get reported as their own number.

Step 2: two conditions with SUMIFS

SUMIFS takes the range to add first, then pairs of range and condition:

=SUMIFS($C$2:$C$25,$A$2:$A$25,H8,$B$2:$B$25,"completed")

reads “add up C where A is this category and B is completed”. The same shape on column D gives the returned lines, and the rate is one divided by the other. The refund share divides completed refunds by completed net sales, so it shows the money side: how much of what a category sold came back as refunds.

Reading the result

Textiles and Furniture return the most lines, Kitchen the fewest. In money, Furniture is the category that hurts: its refunds are the largest in dollars because each item is expensive. Outdoor shows a gap between the two measures: 3.2% of lines but 2.8% of sales, partly because some changed-mind refunds on large items keep a 15% restocking fee.

The sheet cannot say why items come back. The reasons are in the full returns table, and the recommendation uses them.

Pitfalls

• A text condition goes in quotes: "completed", not completed.
• Dividing two rounded displays instead of two cells gives slightly different rates; always divide the cells.
• Typing a number into a rate cell breaks the sheet the next time the export is refreshed.