Lookup & Reference
Running Occurrence Count by Customer or SKU (Expanding COUNTIF)
Number each repeat occurrence of an item sequentially (1st, 2nd, 3rd order per customer) using an anchored expanding range =COUNTIF($A$2:A2, A2) and build unique composite lookup keys.
Tag 1st-time vs repeat customer orders and build unique Nth-match keys!
A full-column =COUNTIF($A$2:$A$7, A2) stamps the TOTAL lifetime count on every row—so a 3-order customer shows "3" on their first, second, AND third purchase! Cohort retention tables, first-order attribution, and Nth-occurrence lookups need a Running Occurrence Number (1 on the first purchase, 2 on the second, 3 on the third)!
Locking ONLY the top row of the range while leaving the bottom row relative—=COUNTIF($A$2:A2, A2)—creates an Expanding Range that grows row by row as you copy down ($A$2:A2 on row 2, $A$2:A3 on row 3, $A$2:A6 on row 6)! Concatenating A2 & "-" & COUNTIF($A$2:A2, A2) turns duplicate customer IDs into unique sequential keys ("CUST-10-1", "CUST-10-2", "CUST-10-3")!
| Row | Customer_ID (A) | Expanding Range Formula | Occurrence # & Composite Key |
|---|---|---|---|
| Row 2 | CUST-10 | =COUNTIF($A$2:A2, A2) | 1 -> "CUST-10-1" (First Order!) |
| Row 3 | CUST-25 | =COUNTIF($A$2:A3, A3) | 1 -> "CUST-25-1" (First Order!) |
| Row 4 | CUST-10 | =COUNTIF($A$2:A4, A4) | 2 -> "CUST-10-2" (2nd Repeat) |
| Row 5 | CUST-10 | =COUNTIF($A$2:A5, A5) | 3 -> "CUST-10-3" (3rd Repeat) |
Expanding-Range Running Occurrence Number
Counts how many times the value in row r has appeared from row 2 through row r (returning "" if A is blank).
=IF(A5 = "", "", COUNTIF($A$2:A5, A5))Composite Nth-Occurrence Key & First-Order Cohort Count
Combines customer ID and running count into a unique key (e.g., "CUST-10-3"), while COUNTIF(B2:B6, 1) counts distinct first-time customers.
=A5 & "-" & COUNTIF($A$2:A5, A5)Try the example
Use the original values or change them to test your understanding.