Excel guide · 3 min read

Running count of occurrences in Excel

Q: How do I count occurrences of a value as I go down a column?

=COUNTIF($B$2:B2,B2) in the first row, filled down. Each row shows how many times its value has appeared up to that point.

Q: How do I find the nth occurrence of a value?

In Excel 365, =INDEX(FILTER(return_range,lookup_range=value),n). In older versions, build a running-count key such as Acme-3 and look it up with MATCH.

Quick answerUse =COUNTIF($B$2:B2,B2) in row 2 and fill it down. The range starts at a locked $B$2 and ends at the current row, so it grows as you copy it, and each row shows how many times its value has appeared so far: 1 for the first Acme ticket, 2 for the second and so on.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

	A	B	C	D	F
1	Ticket	Customer	Priority	Opened	Customer
2	T-301	Acme	High	Nov 2, 2026	Acme
3	T-302	Brio	Low	Nov 2, 2026
4	T-303	Acme	Low	Nov 3, 2026
5	T-304	Corvid	High	Nov 3, 2026
6	T-305	Acme	High	Nov 4, 2026
7	T-306	Brio	High	Nov 5, 2026
8	T-307	Acme	Low	Nov 5, 2026
9	T-308	Corvid	Low	Nov 6, 2026

Download this sheet as an Excel practice file (.xlsx)

The expanding range

$B$2 is an absolute reference, so the start of the range never moves. B2 is relative, so when you fill down it becomes B3, B4, B5. In row 6 the formula reads =COUNTIF($B$2:B6,B6): count how many times Acme appears from the top down to this row.

This is the same trick as a running total with SUM($B$2:B2), except COUNTIF counts matches instead of adding numbers.

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

Result: 3

3: ticket T-305 is Acme's third ticket (after T-301 and T-303).

Show the count out of the total

Add a second COUNTIF over the whole column for the total, and join the two. It tells a support lead at a glance which customers keep coming back.

Note the total: Acme shows 3, but there are four Acme tickets. The next section shows why.

=COUNTIF($B$2:B6,B6)&" of "&COUNTIF($B$2:$B$9,B6)

Result: 3 of 3

3 of 3: the fourth Acme ticket (row 8) is not being counted.

The gotcha: trailing spaces start a new count

Row 8 says "Acme " with a space at the end, probably from a copy and paste. COUNTIF treats it as a different customer, so its running count restarts at 1 and the totals are wrong. Nothing on screen gives it away.

Fix the data: put =TRIM(B2) in a helper column, fill it down, then copy it and paste it over column B with Paste Special > Values. To see the damage first, use TRIM inside the criteria. The formula below counts the clean "Acme" cells down to row 8; row 8 itself is not among them.

=COUNTIF($B$2:B8,TRIM(B8))

Result: 3

3 Acme tickets above row 8 with the clean name, so T-307 is really Acme's fourth.

First or repeat

A running count of 1 means the first time a value appears. Wrap it in IF to label first contacts, first orders or first-time visitors. Unlike COUNTIF over the whole column, this marks the first copy differently from the later ones.

=IF(COUNTIF($B$2:B4,B4)=1,"First","Repeat")

Result: Repeat

Repeat: T-303 is Acme's second ticket.

Build an ID like Acme-03

Join the value and its running count to create a key that is unique for every row. TEXT pads the number to two digits so Acme-03 sorts before Acme-10. These keys are also how you look up "the third ticket for Acme" with a normal lookup.

In Excel 365 you can skip the helper column for that lookup: =INDEX(FILTER(A2:A9,B2:B9=F2),3) returns T-305, Acme's third ticket.

=B6&"-"&TEXT(COUNTIF($B$2:B6,B6),"00")

Result: Acme-03

Acme-03 for ticket T-305.

Running count with a condition

COUNTIFS uses the same expanding ranges for every column. To number only high-priority tickets per customer, add the priority column with its own expanding range and the condition "High".

In an Excel table, write the range with INDEX so it still grows: =COUNTIF(INDEX([Customer],1):[@Customer],[@Customer]).

=COUNTIFS($B$2:B6,B6,$C$2:C6,"High")

Result: 2

2: T-305 is Acme's second high-priority ticket (after T-301).

Common mistakes

Forgetting the $ on the start of the range

Without $B$2 both ends move as you fill down, and every row counts only itself. Use $B$2:B2.

Counts change after sorting

A running count depends on row order. To keep IDs fixed, copy the column and use Paste Special > Values before sorting.

Spaces or different spellings restart the count

Clean the column with TRIM and consistent spelling first.

Slow on very long lists

Each row checks every row above it. On tens of thousands of rows, sort by the value and use =IF(B3=B2,C2+1,1) instead.

FAQ

How do I count occurrences of a value as I go down a column?

=COUNTIF($B$2:B2,B2) in the first row, filled down. Each row shows how many times its value has appeared up to that point.

How do I number duplicates in Excel?

Use the running count: the first copy gets 1, the second 2. Join it to the value, such as =B2&"-"&COUNTIF($B$2:B2,B2), for unique keys.

What is the difference between a running count and a running total?

A running count counts how many rows so far match a value (COUNTIF). A running total adds up the amounts so far (SUM with an expanding range).

How do I find the nth occurrence of a value?

In Excel 365, =INDEX(FILTER(return_range,lookup_range=value),n). In older versions, build a running-count key such as Acme-3 and look it up with MATCH.

Functions used

Related guides

Updated November 2, 2026