Exceljet

Quick, clean, and to the point

Running count of occurrence in list

Excel formula: Running count of occurrence in list
Generic formula 
=IF(A1=value,COUNTIF($A$1:A1,value),"")
Summary 

To create a running count for a specific value that appears in a range of cells, you can use the COUNTIF function with an expanding range. In the example shown, the formula in C5 is:

=IF(B5=value,COUNTIF($B$5:B5,value),"")

where value is the named range E5. The result is a running count of the 4 cells that contain "blue", since E5 contains "blue".

Explanation 

In this example, the goal is to create a running count for a specific value that appears in column B. The value to count is entered in cell E5, which is the named range value. The core of the solution explained below is the COUNTIF function, with help from the IF function to suppress a count for other values.

Basic count

Normally, the COUNTIF function is given a range and criteria like this:

COUNTIF(B5:B16,"blue") // returns 4

Since the range B5:B16 contains 4 cells that contain "blue", COUNTIF returns 4. The formula above works well when you want to single count for one value in a range. However, to get a running count, we'll need to adapt the formula to use an expanding range.

Running count with expanding range

An expanding range is a range with one side that is absolute, and one side that is relative. In this case, we lock the first reference to B5, and leave the second reference relative like this:

$B$5:B5 // expanding range

Notice that the left side of the range is locked ($B$5) and the right side is relative (B5). This is a special kind of "mixed reference", since it contains both absolute and relative addresses. Because the first reference, $B$5, is locked, it doesn't change. However the second reference, B5, changes when the formula is copied. The result is an expanding range. Dropping the expanding range back into the COUNTIF function, we have this formula:

=COUNTIF($B$5:B5,"blue") // returns 1

As the formula is copied down column 1, the range "expands" at each row. You can see the result of the formula above in the screen below:

Basic example of running count of "blue" values

Notice we get a count on every row. However, the count only increments when another "blue" value is encountered.

Removing extra counts

Because we only want to create a running count for one specific value, the next step is to remove the counts that appear next to other values. To do this, we nest the COUNTIF function inside the IF function like this:

=IF(B5=value,COUNTIF($B$5:B5,value),"")

This is the formula entered in cell C5 of the example shown. Notice we have now swapped the hardcoded value "blue" with the named range value (E5). The named range is simply for convenience. It automatically acts like an absolute reference, and it makes the formula easier to read and write.

At each row, the logical test inside of IF tests the value in column B to see if it is equal to the named range value (E5), which contains "blue". If so, the COUNTIF formula explained above is executed, and returns the current running count for "blue". If the value in column B is not equal to "blue", the IF function returns an empty string (""). This effectively removes the count for all other values. The final result is a running count for cells that contain "blue".

Other values

In the attached worksheet, the named range value (E5) is set up to provide a dropdown menu with data validation. If we select a different value, the running count formula immediately returns a new set of running counts. The screen below shows the result of selecting "Green" in E5:

Result of selecting a different value to count

Running count for every value

To create a running count of every value that appears in column B, you can use a generic version of the formula like this:

=COUNTIF($B$5:B5,B5)

As this formula is copied down the table, it returns the running count for every value in column B.

Excel Tables

In an Excel Table, formulas that use standard expanding references can become corrupted when rows are added or removed. You can work around this problem by using a structured reference to create an expanding range like this:

--(INDEX([Value],1):[@Value]=[@Value])

When entered in cell C5, this formula will return a running count for every value in every row. To return a running count for just one value as in the original example above, nest the formula inside IF like this:

=IF(B5=value,SUM(--(INDEX([Value],1):[@Value]=[@Value])),"")

For more information, see the example here.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.