Cellwise Start free

Excel guide · 5 min read

How to calculate product return rate in Excel

Quick answerReturn rate = units returned / units sold. Match each return to the month the item was sold, not the month it came back: =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.

RowABCDEFGHIJKL
1MonthSKUUnits soldRevenueReturn dateSKUOrder dateUnitsRefundReasonType
2Sep 1, 2026JKT-0114016660Sep 22, 2026JKT-01Sep 10, 20263357Too smallRefund
3Sep 1, 2026SHL-0221016590Oct 2, 2026JKT-01Sep 24, 20264476Too smallRefund
4Sep 1, 2026BAG-03954180Oct 6, 2026SHL-02Sep 28, 20266474Not waterproofRefund
5Oct 1, 2026JKT-0118021420Oct 9, 2026JKT-01Sep 30, 20262238Too smallExchange
6Oct 1, 2026SHL-0216012640Oct 14, 2026BAG-03Oct 3, 2026144DamagedRefund
7Oct 1, 2026BAG-031205280Oct 20, 2026SHL-02Oct 8, 20263237Colour not as picturedRefund
8Oct 28, 2026JKT-01Oct 15, 20265595Too largeRefund
9Nov 4, 2026JKT-01Oct 21, 20266714Too smallRefund
10Nov 7, 2026SHL-02Oct 25, 20262158Not waterproofExchange

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.

=SUMIFS(I2:I10,G2:G10,B2,H2:H10,">="&A2,H2:H10,"<="&EOMONTH(A2,0),L2:L10,"Refund")/C2

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.

=SUMIFS(I2:I10,G2:G10,"JKT-01",F2:F10,">="&DATE(2026,10,1),F2:F10,"<="&DATE(2026,10,31),L2:L10,"Refund")/C5

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.

=SUMIFS(J2:J10,H2:H10,">="&DATE(2026,9,1),H2:H10,"<="&DATE(2026,9,30),L2:L10,"Refund")/SUMIFS(D2:D7,A2:A7,DATE(2026,9,1))

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.

=SORTBY(UNIQUE(K2:K10),COUNTIF(K2:K10,UNIQUE(K2:K10)),-1)

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.

=COUNTIFS(G2:G10,"JKT-01",K2:K10,"Too*")/COUNTIF(G2:G10,"JKT-01")

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.

=HSTACK(UNIQUE(B2:B7),SUMIFS(I2:I10,G2:G10,UNIQUE(B2:B7),L2:L10,"Refund")/SUMIFS(C2:C7,B2:B7,UNIQUE(B2:B7)))

Result: spills 3 rows × 2 columns

JKT-010.05625
SHL-020.024324324324
BAG-030.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.

=MAX(F2:F10-H2:H10)

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

Related guides

Updated