=sumproduct() expects arrays as its arguments.

If you want to use application.worksheetfunction.sumproduct, then you'll have to
make those arguments arrays--maybe looping through each cell and returning 1's
or 0's.

And this destroys the idea around using =sumproduct()--why not just loop and
accumulate?

But you could let excel help you by using .Evaluate().  This is like using a
helper cell in the worksheet and letting excel calculate the result.

But when you do this, you have to follow excel's rules.  Unless you're using
xl2007, you can't use the complete column.

Here's a small example with the ranges hardcoded:

Option Explicit
Sub testme()
   Dim customer As String
   Dim myFormula As String
   customer = "asdf"
   
   myFormula = "sumproduct(--(b1:b9=""" & customer & """),g1:g9)"
   MsgBox ActiveSheet.Evaluate(myFormula)
   
End Sub

This is the formula that's being evaluated:
=sumproduct(--(b1:b9="asdf"),g1:g9)

If I used application.evaluate(), then the ranges on the activesheet would be
used.

I could have specified the addresses (including sheet names) or I could use:

workhsheets("sheet9999").evaluate(myformula)
if both ranges were on sheet9999