Excel expression "sumproduct" in Access

F

fatfish

Guest

• Jun 24, 2004

• #1

Hi all,

I'm now building an Access databse and analysis market report.
Does anyone know that how to realize the Excel expression "sumproduct"
in Access?

Sumproduct: Multiplies corresponding components in the given arrays, and returns the sum of those products.
SUMPRODUCT(array1,array2,array3, ...)

It returns similiar result as the formula SUM(A2:B4*C24) entered as an array.

Thanks in advance!!

Best Regards,
FF

 

J

Jon K

Registered User.

Local time

Today, 04:59

Joined

May 22, 2002

Messages

2,209

• Jun 24, 2004

• #2

fatfish said:

Does anyone know that how to realize the Excel expression "sumproduct"
in Access?

Click to expand...

If you make a reference to the Microsoft Excel x.x Object Library (when the Access code window is open, choose menu Tools, References... and select the library) you can then use the SumProduct() function in VBA by passing arrays to it, e.g.

MsgBox WorksheetFunction.SumProduct(array1, array2, etc)
.

 

KenHigg

Registered User

Local time

Yesterday, 23:59

Joined

Jun 9, 2004

Messages

13,327

• Jun 24, 2004

• #3

I'll....be...darn...cool - Thanks for the tip!

 

F

fatfish

Guest

• Jun 25, 2004

• #4

Thanks Jon!

You provide a super solution for Excel expression to Access!
Thanks!