Application.Worksheetfunction.Sumproduct

[Tetsuya Oguma]

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

 

[Bob Phillips]

v = Activesheet.Evaluate("SUMPRODUCT(--(B3:B16-B2:B15>=0),B3:B16-B2:B15)")


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Joel]

Will this do?

Total = 0
for each cell in Range("B3:B16")
if cell >= cell.offset(-1,0) then
total = total + (cell - cell.offset(-1,0))
end if
next cell

The "--" really converts the TRUE's to 1 and the False's to 0. so instead
of getting an array of

{true, false, false, True,...)

You get

{1,0,0,1,...}

Sumproduct doesn't know how to multiply {true * 1}. the -- changes this to
{1 * 1}

 

[Bob Phillips]

Sumproduct doesn't know how to multiply {true * 1}. the -- changes this to
{1 * 1}

=SUMPRODUCT(({TRUE,TRUE})*({1,1}))

=2

 

[Tom Ogilvy]

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.