Calculate Occurrence Number - Excel

Question

I have simple problem, but I've not be able to get an answer from searching. I require a column to calculate the number of the nth occurrence of a value. It's best explained in this picture

I require a method to calculate column B.

I'd be very grateful for any help.

Answer 1

Use the following formula to generate the required series: =COUNTIF($A$1:A1,A1) and strech(copy) it in all the cells

This will generate result like this:

A   1   COUNTIF($A$1:A1,A1)
A   2   COUNTIF($A$1:A2,A2)
C   1   COUNTIF($A$1:A3,A3)
C   2   COUNTIF($A$1:A4,A4)
B   1   COUNTIF($A$1:A5,A5)
B   2   COUNTIF($A$1:A6,A6)
A   3   COUNTIF($A$1:A7,A7)
C   3   COUNTIF($A$1:A8,A8)
D   1   COUNTIF($A$1:A9,A9)
D   2   COUNTIF($A$1:A10,A10)
D   3   COUNTIF($A$1:A11,A11)
D   4   COUNTIF($A$1:A12,A12)

Answer 2

Are you looking to merely provide a count of the distinct entries of column A in column B? Or merely add a formula to come up with the table in your link?

If the latter, then the formula to write in cell B2 is:

=COUNTIF(A$2:A2,A2)

then copy/paste it down column B. Note - if your data is both a Date and Time, but the cell is formatted to only display a date, you may not get the results you want. You'd need to interject a new column with a "floor" calculation to round the date/time value to a date (Excel date times are decimal, with integer part dictating the date, and remaining 0.0 -> 1.0 dictating the time of day)

If you just want to derive a table of the counts of distinct entries in column A, then a pivot table will do this for you - simple add a pivot table to cover the data in column A, then select column A into the rows category, and then also drag it into the values category, ensuring the field is set to "Count of". You should then have a table with the distinct entries in your data set in one column, and the count of their occurrences in the other column.

Answer 3

You can use the COUNTIF worksheet function, with a relative address.

Eg. In cell B2, enter this formula:

=COUNTIF(A$2:A2,A2)

And then fill-down.