Just to add to the information

You can use sumproduct for its original intended purpose of multiplying two
or more arrays element by element and summing the results using
application.Sumproduct.

However your "groovy" formula is using the special ability of sumproduct in
a worksheet to evaluate it arguments as array formulas. This ability is not
supported in VBA. the Evaluate function in VBA can interpret a worksheet
formula supplied as an argument as if it were in a virtual cell. thus Bob
has suggested that use which does support the special abilities of sumproduct
to act as an array formula.

--
Regards,
Tom Ogilvy


"Tetsuya Oguma" wrote:

Hi all,

I have got this groovy formula below as an Excel formula and would like to
make it usable as VBA:

=SUMPRODUCT(--(B3:B16-B2:B15=0),B3:B16-B2:B15)

I realised this "-" in the formula means "multiply by -1".

I tried:

Dim v As Variant
v = Application.WorksheetFunction.SumProduct(--(Range("B3:b16") -
Range("b2:b15") = 0), Range("B3:b16") - Range("b2:b15"))

But I get Type Mismatch error...

Can anyone help?

Thanks in advance,
Tetsuya