# How to get the latest record for each customer in Excel

Keep the rows whose date is the newest for that customer: `=FILTER(A2:C12,B2:B12=MAXIFS(B2:B12,A2:A12,A2:A12))`.

**Excel:** `=FILTER(A2:C12,B2:B12=MAXIFS(B2:B12,A2:A12,A2:A12))` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** `=ARRAYFORMULA(FILTER(A2:C12,COUNTIFS(A2:A12,A2:A12,B2:B12,">"&B2:B12)=0))`

## Example

Sample data (row 1 is the header):

| A | B | C |
| --- | --- | --- |
| Customer | Order date | Amount |
| Contoso | 2026-05-02 | 1200 |
| Fabrikam | 2026-05-09 | 640 |
| Contoso | 2026-05-16 | 980 |
| Northwind | 2026-05-23 | 310 |
| Fabrikam | 2026-05-30 | 455 |
| Contoso | 2026-06-06 | 1475 |
| Northwind | 2026-06-13 | 820 |
| Litware | 2026-06-14 | 2210 |
| Fabrikam | 2026-06-20 | 760 |
| Northwind | 2026-06-03 | 505 |
| Litware | 2026-05-28 | 390 |

Formula in E2: `=FILTER(A2:C12,B2:B12=MAXIFS(B2:B12,A2:A12,A2:A12))`

Result (spills 4 × 3): Contoso, 6/6/2026, $1,475; Northwind, 6/13/2026, $820; Litware, 6/14/2026, $2,210; Fabrikam, 6/20/2026, $760

## How it works

`MAXIFS(B2:B12,A2:A12,A2:A12)` works out, for every row, the latest date of that row's customer. Comparing it with the row's own date is TRUE only on each customer's newest order, so FILTER returns one row per customer. The list doesn't need to be sorted.

The Google Sheets version asks the same question the other way round: keep a row when no order for that customer is later.

## Other ways

- `=FILTER(A2:C12,COUNTIFS(A2:A12,A2:A12,B2:B12,">"&B2:B12)=0)`: Counts later orders for the same customer and keeps rows with none. Same result.
- `=SORT(FILTER(A2:C12,B2:B12=MAXIFS(B2:B12,A2:A12,A2:A12)),1)`: Sort the result by customer name. (Contoso, Fabrikam, Litware, Northwind.)

## Common mistakes

- Two orders for one customer on the same latest date both come back. Add a tie-breaker, such as the higher order number, if you need exactly one.
- Dates stored as text are ignored by MAXIFS, which then returns 0 and drops that customer.

## Questions

**How do I get the first order for each customer instead?**

Use MINIFS: `=FILTER(A2:C12,B2:B12=MINIFS(B2:B12,A2:A12,A2:A12))`.

**How do I return just the latest date per customer?**

Use UNIQUE and MAXIFS: `=HSTACK(UNIQUE(A2:A12),MAXIFS(B2:B12,A2:A12,UNIQUE(A2:A12)))`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/latest-record-for-each-customer