# How to find the latest date for each customer in Excel

Use MAXIFS: `=MAXIFS(dates, customers, E2)` returns the most recent date for the customer in E2.

**Excel:** `=MAXIFS($B$2:$B$9,$A$2:$A$9,E2)` (Excel 2019 and later, including Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Customer | Order date | Amount |  | Customer | Last order |
| Contoso | 2026-01-12 | 420 |  | Contoso |  |
| Fabrikam | 2026-02-03 | 1250 |  | Fabrikam |  |
| Contoso | 2026-03-18 | 310 |  | Northwind |  |
| Northwind | 2026-01-29 | 880 |  |  |  |
| Fabrikam | 2026-04-07 | 1460 |  |  |  |
| Contoso | 2026-02-25 | 95 |  |  |  |
| Northwind | 2026-05-02 | 730 |  |  |  |
| Fabrikam | 2026-03-11 | 515 |  |  |  |

Formula in F2, filled to F4: `=MAXIFS($B$2:$B$9,$A$2:$A$9,E2)`

Result: 3/18/2026; 4/7/2026; 5/2/2026

## How it works

MAXIFS returns the largest value in B2:B9 on the rows where the customer matches E2. Dates are numbers, so the largest one is the most recent. The ranges are locked with $ so they stay put as the formula fills down.

## Other ways

- `=MAX(FILTER($B$2:$B$9,$A$2:$A$9=E2))`: The same result with FILTER, for Excel 365 and Google Sheets.
- `=SUMPRODUCT(MAX(($A$2:$A$9=E2)*$B$2:$B$9))`: Works in Excel 2016 and earlier, which have no MAXIFS.

## Common mistakes

- MAXIFS returns 0, shown as 1/0/1900, for a customer with no orders. Wrap it: `=IF(COUNTIF($A$2:$A$9,E2),MAXIFS(…),"")`.
- Dates stored as text are ignored, which can make the latest date look too early.

## Questions

**How do I get the first (earliest) order date?**

Use MINIFS the same way: `=MINIFS($B$2:$B$9,$A$2:$A$9,E2)`.

**How do I calculate the days since the last order?**

Subtract it from today: `=TODAY()-MAXIFS($B$2:$B$9,$A$2:$A$9,E2)`.

**How do I list every customer automatically?**

In Excel 365 put `=UNIQUE(A2:A9)` in E2, then use `=MAXIFS(B2:B9,A2:A9,E2#)`.

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-date-for-each-customer