Written with AI assistance. I edited it. The formulas below were checked in LibreOffice Calc on sample rows (not in Excel or Google Sheets).
An order list tells you what happened. A customer summary tells you who to call: the regular who hasn't ordered in two months, the one-time buyer worth a follow-up, and the three customers who make up most of your sales.
Here's a one-row-per-customer table built with plain formulas and no pivot table. MINIFS and MAXIFS need Excel 2019, Microsoft 365 or Google Sheets. Everything else works in any version.
1. The order list and the customer list
Orders from row 5: A Date, B Customer, C Amount. Put an As of date in B2 (=TODAY() works).
Then list each customer once in E5:E14, spelled exactly as in column B. To build that list quickly, copy column B and remove duplicates:
- Excel: Data → Remove Duplicates
- Google Sheets: Data → Data cleanup → Remove duplicates
2. One row per customer
| Column | Formula (row 5) |
|---|---|
| F: Total | =SUMIFS($C$5:$C$104,$B$5:$B$104,E5) |
| G: Orders | =COUNTIFS($B$5:$B$104,E5) |
| H: First order | =IF(G5=0,"",MINIFS($A$5:$A$104,$B$5:$B$104,E5)) |
| I: Last order | =IF(G5=0,"",MAXIFS($A$5:$A$104,$B$5:$B$104,E5)) |
| J: Days since last | =IF(G5=0,"",$B$2-I5) |
Format H and I as dates. The IF(G5=0,"",…) guard matters: without it, a customer with no orders shows a last-order date of 0 (shown as 1900 or 1899), and "days since" becomes a 46,000-day nonsense number.
3. Label each customer (K5)
=IF(G5=0,"No orders",IF(G5=1,"One-time",IF(J5>60,"Repeat, gone quiet","Repeat")))
Change 60 to whatever "quiet" means for your business. A café's regular is quiet after two weeks; a wholesale buyer might order once a quarter.
4. Rank and share
Rank by total (L5):
=IF(G5=0,"",RANK(F5,$F$5:$F$14))
Share of all sales (M5, format as %):
=IF($F$17=0,"",F5/$F$17)
F17 is the total of all orders (next section), not just the customers on your list. That way a missing customer makes the shares add up to less than 100% instead of quietly inflating everyone else's.
5. Summary and the check
| Cell | Formula |
|---|---|
| F16: Sum of customer totals | =SUM(F5:F14) |
| F17: All orders | =SUM($C$5:$C$104) |
| F18: Orders with a customer not in the list | =SUMPRODUCT(($B$5:$B$104<>"")*(COUNTIF($E$5:$E$14,$B$5:$B$104)=0)) |
| F19: Check | =IF(ROUND(F16-F17,2)=0,"OK","CHECK") |
| F20: Repeat customers | =COUNTIF(K5:K14,"Repeat*") |
| F21: Customers with orders | =COUNTIF(G5:G14,">0") |
| F22: Repeat rate | =IF(F21=0,"",F20/F21) |
The * in "Repeat*" counts both "Repeat" and "Repeat, gone quiet".
F18 is the important one. A new customer who isn't on your list yet, or a name typed slightly differently, is simply missing from every per-customer number. In my sample, one order from a customer I hadn't added made the check say CHECK and F18 say 1. Add the name to the list and it goes back to OK.
6. What the sample showed
Twelve made-up orders, seven customers on the list (one with no orders yet), as of 20 Oct 2026:
| Customer | Total | Orders | Days since last | Type | Rank |
|---|---|---|---|---|---|
| Asha Traders | 2,700 | 3 | 41 | Repeat | 1 |
| Dutta Mart | 2,500 | 2 | 8 | Repeat | 2 |
| Bose & Co | 1,600 | 2 | 30 | Repeat | 3 |
| Hari Mills | 750 | 2 | 185 | Repeat, gone quiet | 4 |
| Chandra Stores | 450 | 1 | 97 | One-time | 5 |
| Ezra Textiles | 300 | 1 | 15 | One-time | 6 |
| Faiz Stores | 0 | 0 | (blank) | No orders | (blank) |
Repeat rate: 4 of 6 customers with orders. The list total was 8,300 against 9,000 for all orders: the missing 700 was the unlisted customer, so the check said CHECK.
The useful line is Hari Mills: a repeat buyer, quiet for six months. That's the call to make this week.
7. Test it before you trust it
Add rows you know the answer to, then confirm:
- a customer on the list with no orders (should be "No orders", with no 1900 dates)
- a repeat buyer whose last order is older than your "quiet" limit
- one order from a name that isn't on the list (should trip the check)
Then delete them.
Prefer a ready-made file? There's a free Order & Payment Tracker (.xlsx workbook, no macros). You log each order once, and it works out what's still owed and whether it's overdue, by customer and in a one-page summary: https://ashishsrivastavajay.gumroad.com/l/free-order-payment-tracker
About me, and a disclosure: I'm Ashish, a Kolkata-based Excel and Power BI developer (18+ years), and I write these tutorials and build my workbooks with AI assistance under my direction. The free tracker is mine.
Top comments (0)