How to count up events by ID and date

[Hawsie]

I have a report that produces a list of serial numbers and a date as to when an event happened. I am trying to write a formula (without success) that will assign an event number based on how many times that serial number has experienced the event. In the example below the formula would be in the EventNumber column and in the top row would produce "3" because this was the third time a1 experienced the event.

I have only been able to use COUNTIF and produce 3 in each row of a1 indicating the total number of times it has experienced the event. The data is organized as a table, so table references are preferred. I have attached a file with this data.


Serial Number EventDate EventNumber
a1____________1/13/2016__3
b2____________1/12/2016__2
a1____________1/11/2016__2
b2____________1/10/2016__1
c3____________1/5/2016___1
a1____________1/1/2016___1

 

[Nebu]

Hi:

I am not sure what you are looking for. Do you want the count for a combination of the event ID and the date wherein you will count a1-1/13/2016 as 1 , a1-1/11/2016 as 1 and so on . I would suggest you to pull a pivot from the data to get the count you are looking for.

Thanks

 

[Hawsie]

I want to number how many times a1 has a date and number them oldest to newest. The first time a1 appears in the first column the third column should display "1", the second a1 appears in the first column the third column should be numbered "2" etc...

 

[Nebu]

Hi:

Hope this is what you are after.

Thanks

 

[Hawsie]

That is working for me. Thanks Nebu!