Statistical
Running count of occurrences in a list (1st, 2nd, 3rd repeat)
Number each appearance of a customer or SKU sequentially down a table with SUMPRODUCT(--($A$2:A2=A2)) and build unique composite keys.
Count appearances up to the current row only
COUNTIF($A$2:$A$5, A2) counts how many times "Acme" appears in the ENTIRE table (3 on every Acme row). Locking only the top cell—SUMPRODUCT(--($A$2:A2=A2))—counts occurrences from row 2 down to the current row, numbering them 1, 2, 3.
Concatenating A2 & "-" & SUMPRODUCT(--($A$2:A2=A2)) turns duplicate keys into unique tags ("Acme-1", "Acme-2", "Acme-3") for multi-match lookups.
| Row | Client (A) | OrderAmt (B) | Expanding Range | Occurrence # |
|---|---|---|---|---|
| 2 | Acme | 500 | $A$2:A2 | 1 (Acme-1) |
| 3 | Beta | 300 | $A$2:A3 | 1 (Beta-1) |
| 4 | Acme | 750 | $A$2:A4 | 2 (Acme-2) |
| 5 | Acme | 900 | $A$2:A5 | 3 (Acme-3) |
Sequential occurrence number and composite key formulas
Filter Occurrence = 1 anytime you want to isolate only the first appearance of each client.
=SUMPRODUCT(--($A$2:A4=A4))Composite unique lookup key
Row 4 produces "Acme-2" and Row 5 produces "Acme-3".
=A4 & "-" & SUMPRODUCT(--($A$2:A4=A4))Inputs and interpretation
The equality version treats * and ? as literal text, unlike COUNTIF criteria. Names compare case-insensitively; exclude blanks before numbering. These tags describe current row order, not persistent record IDs: sorting or inserting rows can renumber occurrences, and filtering does not remove hidden rows from the range.
Try the example
Use the original values or change them to test your understanding.