+ Reply to Thread
Results 1 to 12 of 12

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

  1. #1
    Forum Contributor
    Join Date
    06-05-2013
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2016
    Posts
    109

    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.

    PFA. Please refer to Sheet1

    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
    Attached Files Attached Files
    Last edited by Vinod Krishna.C; 06-20-2014 at 01:36 PM. Reason: Dynamic Range of Data.

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,937

    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.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,937

    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: copy to clipboard
    Please Login or Register  to view this content.


    instead of
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  4. #4
    Forum Contributor
    Join Date
    06-05-2013
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2016
    Posts
    109

    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.

    Many thanks for your assistance.

    Thanks,
    Vinod Krishna

  5. #5
    Forum Contributor
    Join Date
    06-05-2013
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2016
    Posts
    109

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

    Formula should be:
    Please Login or Register  to view this content.
    Could you all please help with achieving this using Reference to Row and Column i.e. RC
    Ex:- ActiveCell.Formula= "=SUMPRODUCT()"

    Thanks,
    Vinod Krishna

  6. #6
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,937

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

    Hi,

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


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


    Table:
    Formula: copy to clipboard
    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.

  7. #7
    Forum Contributor
    Join Date
    06-05-2013
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2016
    Posts
    109

    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.
    Please help me understand where I'm going wrong.

    Thanks,
    Vinod Krishna
    Attached Files Attached Files

  8. #8
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,937

    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.

  9. #9
    Forum Contributor
    Join Date
    06-05-2013
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2016
    Posts
    109

    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

  10. #10
    Forum Contributor
    Join Date
    06-05-2013
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2016
    Posts
    109

    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

  11. #11
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,937

    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)

  12. #12
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,937

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

    Thanks for the rep. Glad the explanation was of some use.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] A formula and direct entry in the one cell?
    By gogreenpower in forum Excel General
    Replies: 2
    Last Post: 05-19-2014, 09:10 PM
  2. [SOLVED] How to use formula from another cell to direct formula to another sheet?
    By Christopher135 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-16-2013, 10:43 PM
  3. [SOLVED] Excel formula for Direct Debit calculator - Help Please!
    By Millyweb in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 05-28-2013, 04:27 AM
  4. Trouble joining a formula to be direct and indirect
    By curtwphillips in forum Excel General
    Replies: 1
    Last Post: 04-27-2011, 08:27 PM
  5. Is this formula 100*(PRODUCT(1+C3:N3/100)-1) recognised by Excel
    By Chwee Mee in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-17-2006, 06:50 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1