How to number each occurrence of a value in Google Sheets
Google Sheets formula
=ARRAYFORMULA(IF(A2:A11="","",COUNTIFS(A2:A11,A2:A11,ROW(A2:A11),"<="&ROW(A2:A11))))Count matches from the top down to each row: =ARRAYFORMULA(IF(A2:A11="","",COUNTIFS(A2:A11,A2:A11,ROW(A2:A11),"<="&ROW(A2:A11)))).
- Result on the example
- a 10-row list that spills from C2 in
C2 - Excel
- See below
See it work
The formula is already in C2. Change any input and the result updates, or edit the formula to experiment.
How it works
For each row, COUNTIFS counts the rows with the same customer and a row number no higher than this one, which is how many times the customer has appeared so far. ARRAYFORMULA runs it for every row at once. Contoso's rows get 1, 2, 3 and 4 as they appear down the list.
=ARRAYFORMULA(IF(A2:A11="","",COUNTIFS(A2:A11,A2:A11,ROW(A2:A11),"<="&ROW(A2:A11))))Result{10 rows × 1 col} 1, 1, 2, 1, …
A2:A1110 rows × 1 col: "Contoso", "Fabrikam", "Contoso", "Northwind", …A range: 10 cells in one column.
A2:A11=""{10 rows × 1 col} FALSE, FALSE, FALSE, FALSE, …The = operator tests whether the two sides are equal.
ROW(A2:A11){10 rows × 1 col} 2, 3, 4, 5, …ROW: Row number of a reference, or of this cell.- reference
A2:A1110 rows × 1 col: "Contoso", "Fabrikam", "Contoso", "Northwind", …
- reference
ROW(A2:A11){10 rows × 1 col} 2, 3, 4, 5, …ROW: Row number of a reference, or of this cell.- reference
A2:A1110 rows × 1 col: "Contoso", "Fabrikam", "Contoso", "Northwind", …
- reference
"<="&ROW(A2:A11){10 rows × 1 col} "<=2", "<=3", "<=4", "<=5", …The & operator joins as text.
COUNTIFS(A2:A11,A2:A11,ROW(A2:A11),"<="&ROW(A2:A11)){10 rows × 1 col} 1, 1, 2, 1, …COUNTIFS: Counts rows that meet every condition.- criteria_range1
A2:A1110 rows × 1 col: "Contoso", "Fabrikam", "Contoso", "Northwind", … - criteria1
A2:A1110 rows × 1 col: "Contoso", "Fabrikam", "Contoso", "Northwind", … - criteria_range2
ROW(A2:A11){10 rows × 1 col} 2, 3, 4, 5, … - criteria2
"<="&ROW(A2:A11){10 rows × 1 col} "<=2", "<=3", "<=4", "<=5", …
- criteria_range1
IF(A2:A11="","",COUNTIFS(A2:A11,A2:A11,ROW(A2:A11),"<="&ROW(A2:A11))){10 rows × 1 col} 1, 1, 2, 1, …IF: Returns one value if a test is true, another if false.- logical_test
A2:A11=""{10 rows × 1 col} FALSE, FALSE, FALSE, FALSE, … - value_if_true
"" - value_if_false
COUNTIFS(A2:A11,A2:A11,ROW(A2:A11),"<="&ROW(A2:A11)){10 rows × 1 col} 1, 1, 2, 1, …
- logical_test
ARRAYFORMULA(IF(A2:A11="","",COUNTIFS(A2:A11,A2:A11,ROW(A2:A11),"<="&ROW(A2:A11)))){10 rows × 1 col} 1, 1, 2, 1, …ARRAYFORMULA: Runs a formula over whole ranges and returns all the results.- array_formula
IF(A2:A11="","",COUNTIFS(A2:A11,A2:A11,ROW(A2:A11),"<="&ROW(A2:A11))){10 rows × 1 col} 1, 1, 2, 1, …
- array_formula
In Excel
=COUNTIF($A$2:A2,A2) down from C2: the range grows by one row each time.Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =ARRAYFORMULA(IF(A2:A11="","",COUNTIF(A2:A11,A2:A11))) | spills 10 × 1Different result | Without the row test you get each customer's total count on every row. Contoso shows 4 on all four of its rows. |
Common mistakes
- The numbering follows the row order, not the dates. Sort the data by date first if the list isn't in order.
- A fill-down
=COUNTIF($A$2:A2,A2)gives the same numbers, but it has to be filled again whenever rows are added.
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.
Number each occurrence of a value
C2, write one formula that numbers each customer's visits in order: 1 for their first row, 2 for their second, and so on.Questions
How do I keep only each customer's first visit?
Filter on the number: =FILTER(A2:B11,C2:C11=1).
How do I make IDs like Contoso-2?
Join the count to the name: =ARRAYFORMULA(IF(A2:A11="","",A2:A11&"-"&COUNTIFS(A2:A11,A2:A11,ROW(A2:A11),"<="&ROW(A2:A11)))).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine.