upward counting instances of matching multiple criteria

[indesignkat]

In a very large data table I have ID numbers in column A, many other columns of data, then at the end a column that flags rows as needing attention. I use this to create a worklist. My column at the end is a simple nested IF statement. If all 3 criteria are met it returns a 1, otherwise it returns a 0.

Is there a way (that doesn't involve macros and doesn't add too awful much processing time in a 65,000 row, 30 column worksheet) for it to return a 1 the first time a given ID hits on all three criteria, then a 2 the second time, etc?

 

[Somendra Misra]

Hi,

Kindly upload a sample file.

Regards,

 

[indesignkat]

Here you go

 

[Somendra Misra]

Here you go

Kindly try below formula in E2 and copy down:

=IF(AND(B2=$L$2,C2=$M$2,D2=""),COUNTIFS(A$2:A2,A2,B$2:B2,$L$2,C$2:C2,$M$2,D$2:D2,""))

L2 = Process
M2 = 7

Regards,