How to find the latest date for each customer in Excel
Formula (Excel and Google Sheets)
=MAXIFS($B$2:$B$9,$A$2:$A$9,E2)Use MAXIFS: =MAXIFS(dates, customers, E2) returns the most recent date for the customer in E2.
- Result on the example
- one result per row in
F2, filled down toF4 - Works in
- Excel 2019 and later, including Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in F2, filled down to F4. Change any input and the result updates, or edit the formula to experiment.
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.
=MAXIFS($B$2:$B$9,$A$2:$A$9,E2)Result46099
$B$2:$B$98 rows × 1 col: 1/12/2026, 2/3/2026, 3/18/2026, 1/29/2026, …A range: 8 cells in one column.
$A$2:$A$98 rows × 1 col: "Contoso", "Fabrikam", "Contoso", "Northwind", …A range: 8 cells in one column.
E2"Contoso"The value in E2.
MAXIFS($B$2:$B$9,$A$2:$A$9,E2)46099MAXIFS: Largest value that meets every condition.- max_range
$B$2:$B$98 rows × 1 col: 1/12/2026, 2/3/2026, 3/18/2026, 1/29/2026, … - criteria_range1
$A$2:$A$98 rows × 1 col: "Contoso", "Fabrikam", "Contoso", "Northwind", … - criteria1
E2"Contoso"
- max_range
In Google Sheets
The same formula works in Google Sheets: =MAXIFS($B$2:$B$9,$A$2:$A$9,E2). We ran it in Google Sheets mode on the same data and it returns the same results.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =MAX(FILTER($B$2:$B$9,$A$2:$A$9=E2)) | 3/18/2026, 4/7/2026, 5/2/2026 | The same result with FILTER, for Excel 365 and Google Sheets. |
| =SUMPRODUCT(MAX(($A$2:$A$9=E2)*$B$2:$B$9)) | 3/18/2026, 4/7/2026, 5/2/2026 | 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.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Find the latest date for each customer
F2, return the most recent order date for the customer in E2, and fill down to F4.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#).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.