Statistical
Flag customers as New vs Returning on every order row
Compare each order date against MINIFS earliest purchase date per customer so unsorted order exports still classify accurately.
Why MINIFS on OrderDate beats expanding COUNTIF($A$2:A2, A2)=1
An expanding COUNTIF($A$2:A2, A2)=1 labels whichever row appears physically first in the sheet as "New". If your CRM export is sorted newest-first or mixed by region, the customer's most recent order gets mislabeled "New"!
Comparing OrderDate = MINIFS($B$2:$B$4, $A$2:$A$4, A2) finds the customer's true chronological first purchase date regardless of sort order.
This finds the earliest date in the supplied range, not necessarily the customer’s first purchase ever. Include complete history and valid IDs/dates; equal timestamps can still tie and require an explicit order-ID tie-breaker if only one row may be New.
| CustomerId (A) | OrderDate (B) | Earliest Date | Cohort Flag |
|---|---|---|---|
| C-101 | 2026-01-12 | 2026-01-12 | New |
| C-102 | 2026-02-03 | 2026-02-03 | New |
| C-101 | 2026-03-18 | 2026-01-12 | Returning |
Sort-independent New vs Returning formula
On January 12, C-101 matches its earliest date ("New"); on March 18, 2026-03-18 > 2026-01-12 returns "Returning".
=IF(B2 = MINIFS($B$2:$B$4, $A$2:$A$4, A2), "New", "Returning")Backdated historical order test
If row 4 is updated to an imported historical order from 2025-11-20, MINIFS shifts C-101's earliest date to 2025-11-20 and automatically flips row 2 from "New" to "Returning".
=IF(B4 = MINIFS($B$2:$B$4, $A$2:$A$4, A4), "New", "Returning")Try the example
Use the original values or change them to test your understanding.