Excel guide · 5 min read
How to calculate product return rate in Excel
=SUMIFS(returned_units,sku,"JKT-01",order_date,">="&month_start,order_date,"<="&EOMONTH(month_start,0))/units_sold. For the jacket in the sample, 7 of 140 September units were refunded, a 5.0% return rate.Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E | F | G | H | I | J | K | L |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Month | SKU | Units sold | Revenue | Return date | SKU | Order date | Units | Refund | Reason | Type | |
| 2 | Sep 1, 2026 | JKT-01 | 140 | 16660 | Sep 22, 2026 | JKT-01 | Sep 10, 2026 | 3 | 357 | Too small | Refund | |
| 3 | Sep 1, 2026 | SHL-02 | 210 | 16590 | Oct 2, 2026 | JKT-01 | Sep 24, 2026 | 4 | 476 | Too small | Refund | |
| 4 | Sep 1, 2026 | BAG-03 | 95 | 4180 | Oct 6, 2026 | SHL-02 | Sep 28, 2026 | 6 | 474 | Not waterproof | Refund | |
| 5 | Oct 1, 2026 | JKT-01 | 180 | 21420 | Oct 9, 2026 | JKT-01 | Sep 30, 2026 | 2 | 238 | Too small | Exchange | |
| 6 | Oct 1, 2026 | SHL-02 | 160 | 12640 | Oct 14, 2026 | BAG-03 | Oct 3, 2026 | 1 | 44 | Damaged | Refund | |
| 7 | Oct 1, 2026 | BAG-03 | 120 | 5280 | Oct 20, 2026 | SHL-02 | Oct 8, 2026 | 3 | 237 | Colour not as pictured | Refund | |
| 8 | Oct 28, 2026 | JKT-01 | Oct 15, 2026 | 5 | 595 | Too large | Refund | |||||
| 9 | Nov 4, 2026 | JKT-01 | Oct 21, 2026 | 6 | 714 | Too small | Refund | |||||
| 10 | Nov 7, 2026 | SHL-02 | Oct 25, 2026 | 2 | 158 | Not waterproof | Exchange |
Download this sheet as an Excel practice file (.xlsx)
Keep the original order date on every return
The sales table (A:D) has units and revenue by month and SKU, with each month stored as its first day. The returns log (F:L) has one row per return: return date, SKU, the original order date, units, refund amount, reason and whether it was a refund or an exchange.
The order date is the column that makes the rate honest. Returns lag sales: a jacket bought on 24 Sep comes back on 2 Oct. If you divide October's returns by October's sales, a busy month's returns get charged to the next month and your rate swings for no reason.
Return rate by SKU, matched to the sale month
For the jacket's September rate, sum the refunded units whose order date falls in September and divide by September units sold (row 2). The Type criterion leaves out exchanges, which many businesses count separately because the sale wasn't lost. Drop that criterion if yours counts them.
Fill the formula down the sales table with the SKU and month taken from each row (B2 and A2 instead of the typed values) to get a rate for every SKU and month.
Result: 0.05 shows as 5.0% with Percent format
0.05, 5.0%: 7 of the 140 jackets sold in September were refunded (the 2 exchanged ones aren't counted).
What would the by-return-date method say?
Counting jacket returns received in October against October sales mixes two months of buyers.
Result: 0.05
0.05: 9 refunds received in October over 180 sold. It happens to match here, but 4 of those 9 jackets were bought in September.
Why does the latest month look so good?
Its returns haven't all arrived yet. If customers can return within 30 days, October's rate isn't final until late November. Show recent months as provisional, or only report months older than your return window.
Refund value as a % of sales
A return rate by units treats a 44 bag the same as a 119 jacket. For the money view, sum the refunds for the sale month and divide by that month's revenue. Total the whole sales table for a store-wide figure.
Result: 0.034918514561 shows as 3.5% with Percent format
0.0349, 3.5% of September revenue was refunded: 1,307 of 37,430.
Count the reasons and sort them
The rate tells you there's a problem; the reasons tell you what to fix. In Excel 365, SORTBY with UNIQUE and COUNTIF lists every reason, most common first. In older Excel, type the reasons in a column and use =COUNTIF(K$2:K$10,N2) next to each, then sort.
"Too small" leads with 4 of the 9 returns, all on the jacket, which points at a size chart rather than quality. Count units with SUMIFS instead of rows with COUNTIF if one return can hold several items.
Result: spills 5 rows × 1 column
| Too small |
| Not waterproof |
| Damaged |
| Colour not as pictured |
| Too large |
Spills the reasons from most to least common: Too small (4), Not waterproof (2), then Damaged, Colour not as pictured and Too large (1 each).
Are all of one product's returns about fit?
Put the SKU and a wildcard reason in COUNTIFS and divide by the SKU's returns. "Too*" matches both Too small and Too large.
Result: 1
1: every jacket return was a size problem, which is a size-chart fix rather than a supplier complaint.
Return rate for every SKU in one formula
In Excel 365, you don't need a helper table to rank products. UNIQUE lists each SKU once, and because SUMIFS accepts a list as its criteria, it returns one total per SKU. Divide refunded units by units sold and HSTACK puts the names next to the rates.
Read this as a ranking, not a final figure. It divides every return logged so far by all sales, so the latest month, whose returns are still arriving, pulls each rate down a little. For reporting, use the sale-month formula above; for spotting the problem product, this is quicker. In older Excel, type the SKUs in a column and fill a SUMIFS/SUMIFS formula down next to them.
Result: spills 3 rows × 2 columns
| JKT-01 | 0.05625 |
| SHL-02 | 0.024324324324 |
| BAG-03 | 0.0046511627907 |
Spills JKT-01 0.05625, SHL-02 0.0243 and BAG-03 0.0047: the jacket's refund rate (5.6%) is more than twice the shell's (2.4%) and about twelve times the bag's (0.5%).
How long to wait before a month's rate is final
The log already tells you how far returns lag sales: subtract each order date from its return date. Excel stores dates as day numbers, so F2:F10-H2:H10 gives the days each item was kept, and MAX shows the longest wait in the log.
In the sample every return came back within 14 days, with an average of about 11 (=AVERAGE(F2:F10-H2:H10)). So September's rate was safe to report by mid-October, and October's isn't final until about 14 November. With a longer log, =PERCENTILE.INC(F2:F10-H2:H10,0.9) gives the day by which 90% of returns have arrived, which is a fairer cut-off than the single slowest return. Your official return window sets the hard limit.
Result: 14
14: the longest gap between an order and its return in this log, in days.
Which return rate to report
Whichever you pick, show the latest month as provisional until its return window has closed, and check that the units in your returns log can't exceed units sold for any SKU and month. If they do, returns are being matched to the wrong month or the wrong SKU.
- Units returned / units sold, by sale month: the default. It answers "how often does this product come back?" and doesn't swing with seasonality.
- Refund value / revenue: for finance and margin conversations. One expensive item counts more than several cheap ones.
- Returns received this month / sales this month: only for workload planning in the returns team, because it mixes buyers from different months.
- With or without exchanges: decide once, write it next to the figure and keep it. Changing the rule halfway through a year makes the trend meaningless.
Common mistakes
Dividing this month's returns by this month's sales
Match each return to its original order date so returns are charged to the month they were sold.
Reporting the latest month as final
Returns keep arriving until the return window closes. Mark recent months provisional.
Counting exchanges as returns without saying so
Add a Type column and decide whether exchanges count. Put the rule next to the result.
Counting rows instead of units
One return can include several items. SUMIFS the units column rather than COUNTIFS the rows.
FAQ
What is the formula for return rate?
Return rate = units returned / units sold for the same period, usually the sale period. In Excel: =SUMIFS(returned units, criteria)/units sold.
Should return rate use units or revenue?
Both are useful. Units shows how often products come back; refund value as a % of revenue shows the money impact, and expensive items weigh more there.
How do I calculate return rate by product in Excel?
Use SUMIFS on the returns log with the SKU as a criterion, divided by that SKU's units sold. Fill it down a table with one row per SKU and month.
Do exchanges count as returns?
That's a business decision. Many shops track them separately because the sale was kept. Keep a Type column so you can report either way.
How do I calculate the overall return rate for the whole store?
Sum all refunded units and divide by all units sold: =SUMIFS(I2:I10,L2:L10,"Refund")/SUM(C2:C7) gives 28 of 905, about 3.1%, in the sample. Leave out months whose return window is still open, or the figure will creep up later.
Functions used
SUMIFSAdds with several conditionsEOMONTHLast day of a monthDATEBuilds a dateSORTBYSorts by other columns, spillsUNIQUERemoves duplicates, spillsCOUNTIFCounts cells that matchCOUNTIFSCounts with several conditionsHSTACKJoins arrays side by sideMAXLargest numberAVERAGEMean of numbersPERCENTILE.INCValue at a percentile
Related guides
- How to calculate scrap rate in Excel
- How to calculate average order value in Excel
- Percent change formula in Excel
- How to calculate order fill rate in Excel
- How to extract month and year from a date in Excel
- How to calculate backorder rate in Excel
- How to calculate call abandonment rate in Excel
Updated