Running Count of a Value

Excel Formulas › Count

All versionsCOUNTIFExpanding range

A running count numbers each occurrence of a value as it appears — 1st, 2nd, 3rd time this customer shows up. The trick is the same expanding range as a running total, but with COUNTIF.

Quick formula: in B2, copied down, to number each occurrence of A2:
=COUNTIF($A$2:A2, A2)
The locked start $A$2 with the relative end A2 counts how many times the current value has appeared so far.

Functions used (tap for the full reference guide):

COUNTIFCOUNTIFS

The example

Number each customer’s visits in order.

AB
1CustomerVisit #
2Acme1
3Bolt1
4Acme2
5Acme3

The formula

The running count in B2, filled down:

=COUNTIF($A$2:A2, A2) // Acme: 1, then 2, then 3

How it works

The expanding range counts occurrences up to the current row:

  1. The range starts anchored at $A$2 and ends at the relative A2, so it grows one row taller as you copy down.
  2. In each row, COUNTIF counts how many times the current value appears from the top down to here.
  3. The first Acme is 1, the second is 2, the third is 3 — a per-value occurrence number.
  4. It’s the building block for “flag only repeats,” deduping, and pulling the nth match.

Mark the first occurrence only: =IF(COUNTIF($A$2:A2, A2)=1, "First", "") — the running count equals 1 exactly once per value.

Try it: interactive demo

Live demo

Type a list; each item is numbered by how many times it has appeared.

Variations

Flag the first occurrence

Running count of 1 = first time seen:

=IF(COUNTIF($A$2:A2, A2)=1, "First", "Dup")

Running count within a group

Number visits per customer per month with COUNTIFS:

=COUNTIFS($A$2:A2, A2, $B$2:B2, B2)

Make a unique helper key

Combine value and count for a unique ID:

=A2 & "-" & COUNTIF($A$2:A2, A2)

Pitfalls & errors

Same number on every row. The start of the range isn’t anchored. It must be $A$2:A2 — locked start, relative end.

Grand count vs running count. Locking both ends ($A$2:$A$5) gives the total occurrences on every row, not a running one.

Case-insensitive. COUNTIF treats “acme” and “Acme” as the same. Use a case-sensitive approach if that matters.

Practice workbook

📊
Download the free Running Count of a Value practice workbook
A visit list with the live running count, the first-occurrence flag, per-group count, and unique-key variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I create a running count in Excel?
Use =COUNTIF($A$2:A2, A2) and fill it down. The anchored start and relative end make the range expand, so each row shows how many times that value has appeared so far.
How do I number occurrences within a group?
Use COUNTIFS with two expanding ranges: =COUNTIFS($A$2:A2, A2, $B$2:B2, B2) counts occurrences of the value within its group up to the current row.
How do I flag the first occurrence of each value?
Test for a running count of 1: =IF(COUNTIF($A$2:A2, A2)=1, "First", "Dup"), which is TRUE exactly once per value.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Running total · Flag duplicates · Look up the Nth match

Function references: COUNTIF · COUNTIFS