# How to number each occurrence of a value in Google Sheets

Count matches from the top down to each row: `=ARRAYFORMULA(IF(A2:A11="","",COUNTIFS(A2:A11,A2:A11,ROW(A2:A11),"<="&ROW(A2:A11))))`.

**Google Sheets:** `=ARRAYFORMULA(IF(A2:A11="","",COUNTIFS(A2:A11,A2:A11,ROW(A2:A11),"<="&ROW(A2:A11))))`

**Excel:** In Excel, fill `=COUNTIF($A$2:A2,A2)` down from C2: the range grows by one row each time.

## Example

Sample data (row 1 is the header):

| A | B | C |
| --- | --- | --- |
| Customer | Date | Visit # |
| Contoso | 2026-05-02 |  |
| Fabrikam | 2026-05-04 |  |
| Contoso | 2026-05-09 |  |
| Northwind | 2026-05-11 |  |
| Fabrikam | 2026-05-16 |  |
| Contoso | 2026-05-20 |  |
| Litware | 2026-05-23 |  |
| Northwind | 2026-05-27 |  |
| Contoso | 2026-05-30 |  |
| Litware | 2026-06-02 |  |

Formula in C2: `=ARRAYFORMULA(IF(A2:A11="","",COUNTIFS(A2:A11,A2:A11,ROW(A2:A11),"<="&ROW(A2:A11))))`

Result (spills 10 × 1): 1; 1; 2; 1; 2; 3; 1; 2; 4; 2

## 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.

## Other ways

- `=ARRAYFORMULA(IF(A2:A11="","",COUNTIF(A2:A11,A2:A11)))`: 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.

## 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))))`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/number-each-occurrence-of-a-value