I'm not sure if this works or not, but try this one. I don't know your
worksheet's name that has data, so I presume your data always reside in
the first worksheet from left in the selected file.

Sub tracking_test()
Const ad1 = "$aq$2:$aq$43735"
Const ad2 = "$ag$2:$ag$43735"
Const ad3 = "H4"
Dim Acwk As Workbook, Tarwk As Workbook
Dim fname As String, shname As String
Dim arg1, arg2

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set Acwk = ActiveWorkbook

If Not Application.Dialogs(xlDialogOpen).Show Then
Exit Sub
End If

Set Tarwk = ActiveWorkbook
fname = Tarwk.Name
shname = Tarwk.Worksheets(1).Name
Acwk.Activate
Worksheets("actual").Select
arg1 = Application.Evaluate("--([" & fname & "]" & shname & "!" & _
ad1 & " = " & ad3 & ")")
arg2 = Application.Evaluate("--([" & fname & "]" & shname & "!" & _
ad2 & ")")
Range("al12") = Application.SumProduct(arg1, arg2)
Tarwk.Close
End Sub

Keiji

Chechu wrote:
I have the following macro:

Sub tracking()

engid = "'[Export January.xls]Export_January'!$aq$2:$aq$43735"
rev = "'[Export January.xls]Export_January'!$ag$2:$ag$43735"

engid2 = Sheets("actual").Range("H4").Value

If Not IsNumeric(engid2) Then
engid2 = """" & engid2 & """"
End If
pct = Application.Evaluate("SUMPRODUCT(--(" & engid & "=" _
& engid2 & "),--(" & rev & "))")

ThisWorkbook.Worksheets("actual").Range("AL12").Se lect
Range("al12").Value = pct

End Sub

It works OK but, source file is hard coded. What I want to do is to
allow the user to select the source file, and probably open the file
(sumproduct in VBA works with closed files??? I don’t think so). I
know I should include something like:

Set fd = Application.FileDialog(msoFileDialogOpen)
With fd
.AllowMultiSelect = False
If .Show = -1 Then
Set sourcebook = Workbooks.Open(Filename:=fd.SelectedItems(1))
With sourcebook….???????

……..But how to assign to a variable the file name for the selected
file, and also, how to modify the sumproduct syntax to use it???

Thanks,
Cecilia