Array formula to count data using macro

[abcdabcd]

Hi,

I want to match data from sheet1 with data in sheet2(Reporting matrix) using array formula in order to count the data. I used below macro that doesn't work. can any one help me to complet this. My thanks in advance!


Sub Count()
Dim Row As Integer, Col As Integer
Dim x As Integer
Dim y As Integer
Dim DD, TT, CID As Long
DD = Sheet1.Range("A2:A65536").End(xlUp)
TT = Sheet1.Range("C2:C65536").End(xlUp)
CID = Sheet1.Range("B2:B65536").End(xlUp)
x = 3
y = 2
For Col = 2 To 4
For Row = 3 To 6
ActiveSheet.Cells(Row, Col).FormulaArray = "=SUM((A1=Sheet1!DD)*(Sheet1!CID=Sheet2!A"&X&")*(Sheet2!B"&Y&"=Sheet1!TT))"
x = x + 1
y = y + 1
Next Row


Next Col


End Sub