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.
0063
Generic Formula for Running Count of Occurance

=COUNTIF(expanding reference, value)

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.
0064
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 .

=COUNTIF($A$2:A2,A2)

0065
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.

0066
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:
Count Cells that contain specific text
COUNTIFS Two Criteria Match
COUNTIFS With OR For Multiple Criteria
COUNTIF in Excel 2016

Popular Articles:

The VLOOKUP Function in Excel
COUNTIF in Excel 2016
How to Use SUMIF Function in Excel<

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

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.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube