Running Count in Excel: Number Each Occurrence With COUNTIF

A running count in Excel numbers each occurrence of a value as you go down a list: the first time "Lena" appears she gets 1, the second time 2, the third time 3. One COUNTIF formula with a growing range does it, and it is the base for flagging duplicates, building unique IDs and finding the second or third match. This guide shows the formula, why the dollar signs matter, and the most useful ways to put a running count to work.

What is a running count?#

A running count is a count that updates row by row. Each row asks "how many times have I seen this value so far, including now?". The examples use this order list in A1:C8:

Column D is the running count. Lena's third order, in row 7, shows 3. Don't confuse it with a running total, which adds up numbers row by row instead of counting values.

How to make a running count with COUNTIF#

Type this in D2 and fill it down to D8 (Windows: Ctrl+D; Mac: ⌘D):

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

COUNTIF counts the cells in a range that match a value. The trick is the range, $B$2:B2:

• $B$2 is locked with dollar signs, so the start always stays on row 2.
• B2 has no dollar signs, so the end moves down as you fill.

In D7 the formula has become =COUNTIF($B$2:B7,B7). It counts "Lena" in B2:B7 and finds her in rows 2, 4 and 7, so it returns 3. This kind of range is called an expanding range. The COUNTIF function guide covers the syntax, and the COUNTIF article shows other criteria.

Why the dollar signs matter#

The formula only works with the start locked and the end free. Compare what row 7 shows with each version:

The last version looks right in row 7 but shows 3 for every Lena row, including the first. That's a total count, not a running count. Press F4 on Windows or ⌘T on a Mac to cycle through the dollar sign options. The dollar sign guide to absolute references explains mixed references like this one in more detail.

Flag duplicates and first occurrences#

A running count of 1 means "first time seen", and anything higher is a repeat. Label the rows with IF:

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

Rows 4, 6, 7 and 8 show Repeat. To count how many different customers there are, count the 1s in the running count column:

=COUNTIF(D2:D8,1)

The result is 3: Lena, Omar and Ravi. Filter column D to 1 to see a list of first orders only. To colour the repeats instead of labelling them, select B2:B8, choose Home > Conditional Formatting > New Rule > Use a formula, and enter =COUNTIF($B$2:B2,B2)>1. Every second and later occurrence is highlighted. For other ways to find and count repeated values, see how to count duplicates in Excel.

Build unique IDs and find the nth match#

Join the value and its running count to make a key that is unique on every row. In E2:

=B2&"-"&COUNTIF($B$2:B2,B2)

Row 2 shows Lena-1, row 4 Lena-2 and row 7 Lena-3. Now a normal lookup can find any occurrence, not just the first. To get the order number of Lena's second order:

=XLOOKUP("Lena-2",E2:E8,A2:A8)

The result is 1003. XLOOKUP needs Excel 2021 or Microsoft 365; in older versions use =INDEX(A2:A8,MATCH("Lena-2",E2:E8,0)), which returns the same order.

Running count with more than one condition#

COUNTIFS accepts several range and criteria pairs, and each range can expand. To number only each customer's paid orders:

=IF(C2="Paid",COUNTIFS($B$2:B2,B2,$C$2:C2,"Paid"),"")

Lena's paid orders get 1 and 2 (rows 2 and 7), and her open order in row 4 stays blank. Both ranges must start on the same row and grow together, otherwise COUNTIFS returns #VALUE!.

For a plain row number that skips blank rows, use an expanding COUNTA: =IF(B2="","",COUNTA($B$2:B2)). The guide to auto-numbering rows covers that and other numbering methods.

Running counts in tables and long lists#

In an Excel table (Insert > Table), an expanding range such as $B$2:B2 can be adjusted inconsistently when new rows are added. This version always starts at the first row of the column:

=COUNTIF(INDEX([Customer],1):[@Customer],[@Customer])

INDEX([Customer],1) is the first cell of the Customer column and [@Customer] is the current row, so the range still grows by one row each time.

A running count depends on row order. If you sort the list by date or by customer, the numbers recalculate for the new order, so Lena's "first" order may change. When you need numbers that stay fixed, such as IDs you have already sent to someone, copy the column and paste it back as values (Windows: Ctrl+Alt+V; Mac: ⌃⌘V, then choose Values).

Each row's COUNTIF checks every row above it, so the work grows quickly with the length of the list. A few thousand rows is fine; on hundreds of thousands, a pivot table or Power Query is faster.

Practise it#

Try the exercise on this page: one COUNTIF with a half-locked range numbers Lena's third order. Then practise COUNTIF in a live spreadsheet and practise relative and absolute references, where you'll fill formulas down and see exactly which parts move.