# How to See Your Best and Repeat Customers in a Spreadsheet

*Written with AI assistance.*

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 or Microsoft 365. Everything else works in any version of Excel.

## 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

## 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)

```plaintext
=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):

```plaintext
=IF(G5=0,"",RANK(F5,$F$5:$F$14))
```

Share of all sales (M5, format as %):

```plaintext
=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>

Practical spreadsheet templates for small businesses, made with AI assistance.