Hi Johan,

Am Thu, 7 May 2020 06:12:24 -0700 (PDT) schrieb JS SL:

I'd translated the formula to my sheet;
Column A=E, B=N, C=N. It works well :) but..... it still cost many many hours to calculate. Is there a faster way (the fact is that the sheet has 200.000 records)

try this code:

Sub myCount()
Dim LRow As Long, i As Long
Dim Last As Long
Dim Res As Integer
Dim myRng As String

With ActiveSheet
LRow = .Cells(.Rows.Count, "E").End(xlUp).Row
For i = 2 To LRow
Last = i - 1 + Application.CountIfs(.Range("N:N"), _
.Cells(i, "N"), .Range("Q:Q"), .Cells(i, "Q"))
myRng = .Range(.Cells(i, "E"), .Cells(Last, "E")).Address
Res = Evaluate("=Sum(1 / CountIf(" & myRng & "," & myRng & "))")
.Range(.Cells(i, "R"), .Cells(Last, "R")) = Res
i = Last
Next
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016