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?
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.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.