# How to create a running count of occurrences in Excel

Count from the top of the column down to the current row, with only the start locked: `=COUNTIF($B$2:B2,B2)`, filled down.

**Excel:** `=COUNTIF($B$2:B2,B2)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C |
| --- | --- | --- |
| Order | Customer | Customer order no. |
| O-301 | Northwind |  |
| O-302 | Contoso |  |
| O-303 | Northwind |  |
| O-304 | Fabrikam |  |
| O-305 | Contoso |  |
| O-306 | Northwind |  |
| O-307 | Litware |  |
| O-308 | Fabrikam |  |
| O-309 | Contoso |  |
| O-310 | Northwind |  |

Formula in C2, filled to C11: `=COUNTIF($B$2:B2,B2)`

Result: 1; 1; 2; 1; 2; 3; 1; 2; 3; 4

## How it works

The range `$B$2:B2` has a locked start and a free end. Filled down, it grows one row at a time: in C6 it is `$B$2:B6`. So each row counts how often its customer appears from the top down to itself, which numbers the occurrences 1, 2, 3 and so on.

Northwind's orders are numbered 1, 2, 3 and 4 in the order they were placed.

## Other ways

- `=SUMPRODUCT(--($B$2:B2=B2))`: The same expanding range with a direct comparison. Use it when you need a case-sensitive version with EXACT.

## Common mistakes

- Locking both ends (`$B$2:$B$11`) gives the total count on every row instead of a running one.
- Sorting the data after filling changes the numbers, because they depend on row order. Sort first, or copy and paste as values.
- On tens of thousands of rows the expanding range gets slow, since each row rescans everything above it.

## Questions

**How do I flag only the first occurrence?**

Test for 1: `=COUNTIF($B$2:B2,B2)=1` returns TRUE on each customer's first row.

**How do I get the Nth occurrence of a value?**

Filter and pick the row: `=INDEX(FILTER(A2:A11,B2:B11="Northwind"),3)` returns Northwind's third order, O-306.

**How do I build a unique key like Northwind-2?**

Join the name and the count: `=B2&"-"&COUNTIF($B$2:B2,B2)`.

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/running-count-of-occurrences