=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:
How this formula works
Working from the inside out, the COUNTIF function is set up to count the value “bike” with a mixed reference:
COUNTIF($B$5:B5,"bike")
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 “bike”.
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 “bike”. If not, IF returns an empty string (“”).
Note: to freeze both colum and row, you press F4 button or Fn and F4 (if your computer has Fn button) once, twice if you want to lock row and three times with colum only.
