# Excel VBA - Sum Product Formula (Direct Formula or using RC)

1. ## Excel VBA - Sum Product Formula (Direct Formula or using RC)

Dear all,

I would need help to write Sum Product formula in VBA for Dynamic Range of Data.

Input Data : Range A1:D4

Sum Product Logic and Explanation : Range G1:I4

Sum Product formula to be written : Range B9:B11

Note: Autofill formula can be written. I need help with vba for Sum Product.

Many thanks.

Thanks,
Vinod Krishna  Register To Reply

2. ## Re: Excel VBA - Sum Product Formula (Direct Formula or using RC)

Hi,

What results are you expecting in B9:B11?

Or if all you are wanting to do is get the sumproduct formula into say B9 then with VBA ``Please Login or Register  to view this content.``  Register To Reply

3. ## Re: Excel VBA - Sum Product Formula (Direct Formula or using RC)

...but looking a bit further are you sure your formulae in H3:I4 are correct?

Are you sure that I3 for instance shouldn't be

Formula:  `Please Login or Register  to view this content.`

Formula:  `Please Login or Register  to view this content.`  Register To Reply

4. ## Re: Excel VBA - Sum Product Formula (Direct Formula or using RC)

Hi Richard,

Code works great. ``Please Login or Register  to view this content.``
I totally forgot to mention that I would need this for a dynamic range of data.

At the moment, I have the logic to store all 3 Dynamic Ranges i.e. Range of Fruits, Rate and Quantity.

I would like to know the VBA to calculate Sum Product on a dynamic range of data.

Thanks,
Vinod Krishna  Register To Reply

5. ## Re: Excel VBA - Sum Product Formula (Direct Formula or using RC)

Formula should be: ``Please Login or Register  to view this content.``
Ex:- ActiveCell.Formula= "=SUMPRODUCT()"

Thanks,
Vinod Krishna  Register To Reply

6. ## Re: Excel VBA - Sum Product Formula (Direct Formula or using RC)

Hi,

Create 3 dynamic range names. viz.
Fruits:
Formula:  `Please Login or Register  to view this content.`

Rates:
Formula:  `Please Login or Register  to view this content.`

Table:
Formula:  `Please Login or Register  to view this content.`

Then in VBA assuming the formula is going in H2 ``Please Login or Register  to view this content.``  Register To Reply

7. ## Re: Excel VBA - Sum Product Formula (Direct Formula or using RC)

Dear Richard,

With your idea of Naming Ranges, I tried the following method:

PFA for the updated file. ``Please Login or Register  to view this content.``
But I get the error:
Run-time error '1004':
Application-defined or object-defined error

at the line: ``Please Login or Register  to view this content.``

Thanks,
Vinod Krishna  Register To Reply

8. ## Re: Excel VBA - Sum Product Formula (Direct Formula or using RC)

Hi,

I didn't mean you to use VBA to create the dynamic range names. Create them in Excel using the formulae I gave you.  Register To Reply

9. ## Re: Excel VBA - Sum Product Formula (Direct Formula or using RC)

Well, I did give it a try but I just couldn't get it right Then striked the idea of Naming ranges using VBA.

Please be informed that the data is always dynamic. i.e. rows and columns will keep varying.

Kindly help me understand this formula: ``Please Login or Register  to view this content.``
It would be great if this can implemented in the attached excelsheet(updated version).

Many thanks   Register To Reply

10. ## Re: Excel VBA - Sum Product Formula (Direct Formula or using RC)

Giving the name of the range within quotes work: ``Please Login or Register  to view this content.``
Problem Solved   Register To Reply

11. ## Re: Excel VBA - Sum Product Formula (Direct Formula or using RC)

It simply means that the range name in question is defined as starting in an offset of zero rows and zero columns (0,0) from B2. i.e. with those offsets it IS B2, and the height of the range (i.e. rows) is determined by the COUNTA(Sheet1!A:A) function which counts non blank cells in column A, and the width is 3)  Register To Reply

12. ## Re: Excel VBA - Sum Product Formula (Direct Formula or using RC)

Thanks for the rep. Glad the explanation was of some use.  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 