How much has each customer spent, net of refunds?

The data

In a spreadsheet

Refund (on each order row) =SUMIFS(Refunds[Refunded], Refunds[Order], [@Order])
Net per customer           =SUMIFS(Orders[Amount], Orders[Customer], A2) - SUMIFS(Orders[Refund], Orders[Customer], A2)

In SQL

SELECT o.customer,
       COUNT(*) AS orders,
       SUM(o.amount) AS gross,
       COALESCE(SUM(r.amount), 0) AS refunds,
       SUM(o.amount) - COALESCE(SUM(r.amount), 0) AS net
FROM orders o
LEFT JOIN refunds r ON r.order_id = o.order_id
GROUP BY o.customer
ORDER BY net DESC;

Watch out for

• Partial refunds: sum refunds per order before joining, or one order with two refunds is counted twice.
• This is historical value. A predicted lifetime value also needs a churn or retention rate.
• Margins: for profitability, multiply net revenue by gross margin rather than ranking on revenue.

Ask it in Analistable: “Rank customers by net revenue after refunds, with their number of orders.” Related: repeat purchase rate.

In Google Sheets

=QUERY({Orders!A2:A, Orders!D2:D - ARRAYFORMULA(IFERROR(VLOOKUP(Orders!B2:B, Refunds!A2:B, 2, FALSE), 0))},
  "select Col1, sum(Col2) where Col1 is not null group by Col1 order by sum(Col2) desc label sum(Col2) 'Net'", 0)

The VLOOKUP brings each order's refund (0 if none) next to its amount; QUERY sums the net per customer and sorts. This assumes at most one refund row per order — sum refunds per order first if there can be several.

The double-counting trap, with numbers

Suppose O2's 80 refund arrives as two rows: 50 and 30. Joining the raw refunds export to orders duplicates the O2 order row, so ana@lune.fr's gross becomes 375 instead of 295. Aggregate refunds per order first:

WITH r AS (
  SELECT order_id, SUM(amount) AS refunded
  FROM refunds
  GROUP BY order_id
)
SELECT o.customer,
       SUM(o.amount) AS gross,
       COALESCE(SUM(r.refunded), 0) AS refunds,
       SUM(o.amount) - COALESCE(SUM(r.refunded), 0) AS net
FROM orders o
LEFT JOIN r ON r.order_id = o.order_id
GROUP BY o.customer
ORDER BY net DESC;

This returns the same table as above (eva@nordic.dk 300, ana@lune.fr 215, ben@hart.co.uk 60, kim@kiln.io 45) however many refund rows each order has. In a spreadsheet, SUMIFS on the order ID already sums all refund rows, so the formula version is safe.

Summary figures from the same data

Average net value per customer is the historical CLV most teams quote. A simple forward-looking estimate multiplies average order value by expected orders per year and expected years as a customer — but those inputs are assumptions, so show them next to the result.

Troubleshooting

Recalculate monthly on the full history, not month by month, so a refund in October reduces the value of the September order it belongs to. Background on joining the two exports: joining spreadsheets.

Frequently asked questions

How do I calculate customer lifetime value in Excel?

Sum each customer's order totals with SUMIFS and subtract their refunds, which you first match to orders by order ID.

Why join refunds by order ID rather than customer?

Refund exports often lack the customer, and joining by order keeps partial refunds attached to the right purchase.

Is this the same as predicted CLV?

No. This is realised value to date; predicted CLV adds an estimate of future purchases.

Related guides

• How fast is each product's stock turning over?
• How did each product grow from last month to this month?
• What is our repeat purchase rate?
• How much of our revenue does GA4 actually record?
• How do two quotes compare, line by line?