How to do Running Count of Occurance in Excel

Count of running occurrence, what we mean by that. Actually, we will try to get occurance of some text or value in a range by its position. For example, on first occurrence of “A”, we will tag it as 1, on second as 2, on third occurrence 3 and so on. How this is useful? It is very useful. we will see that later in the article.

Generic Formula for Running Count of Occurance

Expanding reference: it is a reference that expands when copied down. In this reference, first part is locked or say have absolute reference and second is relative reference. For example $A$1:A1. To understand more, you can read the article linked above.
Value: the value of which you want to get running count in occurrence. It should be relative.
All of the theory above, may have bored you. Let’s see an example.

Get Running Count of Occurrence Example 1
In this example we have a list of items. We need get running count of occurrence of each item.

Write below formula in B2 and copy or drag down to B12, to get the running count of occurrence of each item in range A2:A12 .

You can see that on first appearance of honey it is marked as 1 on second appearance it is 2 and so on. This happens for each item.

Note that range is expanding in C2:C12 from A2:A2 to A2:A12.

How It Works:
The trick is the expanding range here.  As we know COUNTIF function returns count of a given value in a range.
Now in first cell C2, we have range $A$2:A2 and we are counting A2 in it. Since this range has only one cell and we are counting that only cell, it will return 1 obviously.
Same happens for second cell C3. The range changes to $A$2:A3. We are counting value in A2, since it hasn’t occurred before, we again get 1
Next when we copied the formula downwards to cell C5, the range changes to $A$2:A5. We are counting A5’s value in this range. It has occured before in A1, hence we get 2. And for each value in range.

What is the use of running count of occurrence of values?

1. Imagine in the above list, you just wanted to keep only the first 2 occurrences of vegs. In this case, removing duplicates will not help. So, you can here filter out occurrence greater than 2 and then delete them.
2. You can use to count unique values. Just count 1 in the range and the returned value will be the number of unique values in the range.

Isn’t it useful?
There are many use cases, it depends on the users need and creativity.
I hope this article was helpful. Tell me how you’ll use it. If you have any queries regarding this or any other question regarding excel 2016, 2013, 2010 or any version, feel free to ask in the comments section below.

Related Articles:
How to Count Cells that contain specific text in Excel
Get COUNTIFS Two Criteria Match in Excel
Get the COUNTIFS With OR For Multiple Criteria in Excel
How to use the COUNTIF function in Excel

Popular Articles :

50 Excel Shortcut to Increase Your Productivity : Get faster at your task. These 50 shortcuts will make you work even faster on Excel.

How to use the VLOOKUP Function in Excel : This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.

How to use the COUNTIF function in Excel : Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.

How to use the SUMIF Function in Excel : This is another dashboard essential function. This helps you sum up values on specific conditions.