Running count of occurrence in list

An excel formula to running count of occurrence in list

Related Functions

 Running count of occurrence in list
=COUNTIF($A$1:A1,value)

If you want to a running count of certain values that appear in range of cells, you can use the COUNTIF with a "mixed reference" to create a running total.

In the example shown, the formula in C5 is:

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

How this formula works

Working from the inside out, the  COUNTIF function is set up to count the value "blue" with a mixed reference:

 
COUNTIF($B$5:B5,"blue")

The left side of the range reference is locked ($B$5) and the right side is relative (B5).  As the formula is copied down the column, the first cell in the reference won't change, but the second cell will. This will cause the range that COUNTIF is using to expand with each row. This is sometimes called a "mixed reference", since it contains both absolute and relative addresses.

The result is a "running count" of cells in column B that contain "blue".

The outer layer of the formula uses the IF function to control when COUNTIF fires. COUNTIF only generated a count when the value in B is "blue". If not, IF returns an empty string ("").

0 votes. 0 / 5