WorksheetFunction.SumProduct

[Jon von der Heyden]

Hi,

Why can't I do this?

Cells(17, 6) = WorksheetFunction.SumProduct((iRange >= Cells(15, 6)) * (iRange <= Cells(14, 6)))

Where

Set iRange = Sheets("Results").Range("B2:B" & i)

iRange appears fine throughout so suspect something else is wrong. I get a "Type Mismatch" error.

Any idea?

Thanks,
Jon

 

[hatman]

maybe

Cells(17, 6) = WorksheetFunction.SumProduct(--(iRange >= Cells(15, 6)) ,-- (iRange <= Cells(14, 6)))

 

[Jon von der Heyden]

Hi Hatman, I tried that version 1st but I had the same error. I suspect it's simply not possible to produce a SumProduct based on criteria in VBA? So I've opted for the .Formula = "=SUMPRODUCT(--------" route instead.

Still appreciate it if anybody can suggest why the original method attempted fails...?

Cheers