How to create a running count of occurrences in Excel

Q: 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.

Q: 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.

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

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

Formula (Excel and Google Sheets)

=COUNTIF($B$2:B2,B2)

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

Result on the example: one result per row in C2, filled down to C11
Works in: Every current version
Google Sheets: Same formula

See it work

The formula is already in C2, filled down to C11. Change any input and the result updates, or edit the formula to experiment.

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.

=COUNTIF($B$2:B2,B2)

Result1

$B$2:B2"Northwind"
A range: one cell.
B2"Northwind"
The value in B2.
COUNTIF($B$2:B2,B2)1
COUNTIF: Counts cells that meet one condition.
- range$B$2:B2"Northwind"
- criteriaB2"Northwind"

In Google Sheets

The same formula works in Google Sheets: =COUNTIF($B$2:B2,B2). We ran it in Google Sheets mode on the same data and it returns the same results.

Other ways to do it

Each one was run on the same example sheet.

Formula	Result	When to use it
=SUMPRODUCT(--($B$2:B2=B2))	1, 1, 2…	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.

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.

Create a running count of occurrences

In C2, number each order by how many times its customer has appeared so far, then fill down to C11.

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

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.