[SOLVED] Sequential numbering of duplicates. COUNTIF but showing 1, 2, 3 etc.

Hiya,

I need a formula that will count the duplicate dates in the Short Date column, as I need each event to have a reference number. I also need that reference number to begin at 2, as only Public Holiday events should have a reference of 1.

I have managed to create what I want (in the IFs column) so you can see what I mean, but the problem with it is that if the user deletes an event from anywhere except the bottom, the referencing in the columns is broken and throws up an error. The user will then have to recalculate the whole column any time they delete an event. I want this reference column to be completely automated and hide it from the user, so this doesn't work for me!

I tried using a countif, but couldn't quite get it to work, and found out in the process that I couldn't use countifs anyway, as my original document is large and complex and countifs slow it down way too much!

Hopefully someone can help
Thanks!

If you want the formula to withstand row deletion, then you need to use COUNTIFS. You don't fully explain what "Not an event" means to your numbering scheme, but try this version.

=IF([@Department]="Public Holiday",1,1+MAX(1,COUNTIFS($C$1:C2,C2,$H$1:H2,"Event")))

If that is too slow, then you could disallow row deletions and use a flag that is used in your formula, like "Cancelled event"... or you could use a macro to update the values as values when needed.

Maybe this but still need countifs

C2
=IF(H2="Public Holiday",1,(COUNTIFS(C$2:C2,C2,H$2:H2,"<>Public Holiday")+1))