This article shows how to calculate running count by using COUNTIF and COUNTIFS functions. The running totals help you to track number of value occurrences in your data.
Syntax
=COUNTIF(expanding range of lookup values, lookup value again)
Steps
- Add a new column to the left of your data table and select the first cell
- Type the formula that generates a unique value like =COUNTIF($B$3:B3,B3)
- Copy down the formula to the entire table column
How
The trick is to use an expanding range ($E$3:E3) on the COUNTIF function. The expanding range uses mixed references (absolute and relative) to expand from an anchor cell when you copy it down. The COUNTIF function with an expanding range checks the count of a value only in cells from the beginning to its current location while avoiding the cells after. This behavior returns running count values with each row that it is expanded.
=COUNTIF($B$3:B3,B3)
Alternatively, the COUNTIFS function can also be used because we only need single criteria and both work the same way. If you need more than one criteria, you can set expanding ranges for the other criteria as well.
=COUNTIFS($B$3:B3,B3)
Also see related articles how to count values if date is greater than by using COUNTIF function and how to count values between two dates.
