How to use dictionary method instead countif?

[Crosssover]

I want to use vba dictionary method to calculate over 100k rows instead of in worksheet countif function to reduce time of calculation but I could not find how to convert my formula which shown in below.

Range("AG2").Formula = "=COUNTIF($AF$2:$AF100000,"YES" & X2 & Z2 & "TRUE")"
Range("AG3").Formula = "=COUNTIF($AF$2:$AF100000,"YES" & X3 & Z3 & "TRUE")"
Range("AG4").Formula = "=COUNTIF($AF$2:$AF100000,"YES" & X4 & Z4 & "TRUE")"
...
Range("AG100000").Formula = "=COUNTIF($AF$2:$AF100000,"YES" & X100000 & Z100000 & "TRUE")"

Does anyone help me to convert it dictionary method?

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub crosssover()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long
   
   Ary = Range("X2", Range("AF" & Rows.Count).End(xlUp)).Value2
   ReDim Nary(1 To UBound(Ary), 1 To 1)
   With CreateObject("scripting.dictionary")
      For r = 1 To UBound(Ary)
         .Item(Ary(r, 9)) = .Item(Ary(r, 9)) + 1
      Next r
      For r = 1 To UBound(Nary)
         Nary(r, 1) = .Item("YES" & Ary(r, 1) & Ary(r, 3) & "TRUE")
      Next r
   End With
   Range("AG2").Resize(UBound(Nary), 1).Value = Nary
End Sub

 

[Crosssover]

Thank you for quick response and it worked like jet. Is there any source where can I learn more about dictionary structure, interpret and write codes without help?

 

[Fluff]

You're welcome & thanks for the feedback.
You can get more details on dictionaries here Excel VBA Dictionary - A Complete Guide - Excel Macro Mastery