Using SUMPRODUCT to count values in closed workbook

Hi there,

I'm testing out an application using SUMPRODUCT in worksheetfunction in VBA.(reason i'm using this formula is because the lookup range increases on-going)

What I would like to have Excel do is to give me a total count of "Yes" in a specific site selected,and output that count in cell F2 in the main opened workbook.

The code I have so far is:

Private Sub SUMPCal_Click()
Dim f2 As Long
Dim mSite As Range
Dim mStatus As Range
Dim mCount As Long
Dim bSite As Range 'from closed workbook
Dim bStatus As Range 'from closed workbook
Dim cSite As Range ' from closed workbook
Dim dStatus As ragne 'from closed workbook
   
    mFormula = Application.WorksheetFunction.SumProduct() 'how should the syntax go in bracket especially if the columns are in a closed workbook? do I need to declare them first or?
    
  
    mCount = Application.Evaluate(mFormula)
    Worksheets("display results").Range("F2") = mCount
    
    
End Sub

The lookup columns in data book are: These are in a closed workbook, in sheet "C"

Status Site
Yes c
Yes c
Yes c
No c
No c
Yes c
No c
Yes c
Yes c
No c


in the same closed workbook, in worksheet "B"

Status Site
Yes b
Yes b
Yes b
No b
No b
Yes b
No b
Yes b
Yes b
No b

Thank you for any input or guidance