Date & Time Formulas
Count New (First-Time) Customers Per Month (MINIFS & COUNTIFS)
Distinguish genuine first-time customer acquisitions from repeat monthly orders by tagging each customer’s earliest purchase date with MINIFS and counting monthly cohort entries with COUNTIFS and EOMONTH.
Why counting distinct buyers in March overcounts "New Customers"!
If Acme Corp bought in January and orders again in February, a naive monthly unique count treats Acme as an active February buyer—not a NEW February acquisition! True SaaS and e-commerce cohort accounting defines a New Customer in month M strictly as a customer whose all-time earliest invoice date MINIFS($B$2:$B$7, $A$2:$A$7, A2) falls between the first and last calendar day of month M!
Tagging each invoice row with First_Order_Flag = (B2 = MINIFS($B$2:$B$7, $A$2:$A$7, A2)) turns cohort counting into a fast, auditable COUNTIFS: =COUNTIFS(B2:B7, ">=" & F2, B2:B7, "<=" & EOMONTH(F2, 0), D2:D7, TRUE) (provided no customer places two separate invoices on their exact same first day; if same-day split invoices exist, add a row-order tie-breaker COUNTIFS($A$2:A2, A2, $B$2:B2, B2) = 1!).
| Customer ID | Order Date (ISO Serial) | Earliest Date via MINIFS | First-Time Acquisition Month |
|---|---|---|---|
| CUST-01 | 2026-01-12 | 2026-01-12 (TRUE) | Counted in Jan 2026 Cohort |
| CUST-01 | 2026-02-09 | 2026-01-12 (FALSE) | Repeat Order (Excluded from Feb) |
| CUST-03 | 2026-02-18 | 2026-02-18 (TRUE) | Counted in Feb 2026 Cohort |
New First-Time Customers Acquired in Target Month
Counts rows inside [F2, EOMONTH(F2, 0)] whose First_Order helper flag D2:D7 equals TRUE.
=COUNTIFS(B2:B7, ">=" & F2, B2:B7, "<=" & EOMONTH(F2, 0), D2:D7, TRUE)New-Customer Revenue Collected on First Orders in Target Month
Sums invoice amounts C2:C7 strictly across first-order cohort rows within the target month.
=SUMIFS(C2:C7, B2:B7, ">=" & F2, B2:B7, "<=" & EOMONTH(F2, 0), D2:D7, TRUE)Try the example
Use the original values or change them to test your understanding.