Because just as within XL, VBA doesn't pass (Terms_Market = "Central")
to SumProduct. Instead it evaluates it, but unlike XL's calculation
engine, which evaluates it as an array, VBA doesn't.


Why not

CentralTerms = Application.WorksheetFunction.Countif( _
Terms_Market, "Central")

instead?

In article ,
John wrote:

I am trying to create a VBA function that looks at a Range of data on Sheet2,
and uses the SumProduct function to return the number of occurances in that
range. I keep getting a Type Mismatch error, but I can't figure out why. Any
suggestions?

Sub TermsMarketBreakdown()
' Breaksdown the number of terms by Market
Dim CentralTerms
Dim Terms_Market
Set Terms_Market = Sheet2.Range("E2:E300")
CentralTerms = Application.WorksheetFunction.SumProduct((Terms_Ma rket =
"Central") * 1)
Cells(1, 1) = CentralTerms
End Sub