Consulting

Results 1 to 6 of 6

Thread: sumproduct?

  1. #1

    sumproduct?

    Im trying to do the following if anyone can help.
    Formula cell is A10

    =SUM(B7/Sum(B3,B4,B5))
    Example: Sum ( 10 / Sum ( 1, 1, 3)) = 2

    Now the hard part, i need to complete the above for columns B to G all in the same formula, the only way I can think of doing it is to have an extra row which does the Sum(B3,B4,B5) part.

    Columns B to G are the same format, values will change but for the above example they can all be the same.
    The expected result from the formula would be 12.

    Any suggestions appreciated.

    Thanks
    Last edited by plasteredric; 11-12-2017 at 07:16 AM.

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    What is wrong with

    =B7/SUM(B3:G5)
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Quote Originally Posted by plasteredric View Post
    Im trying to do the following if anyone can help.
    Formula cell is A10

    =SUM(B7/Sum(B3,B4,B5))
    Example: Sum ( 10 / Sum ( 1, 1, 3)) = 2

    Now the hard part, i need to complete the above for columns B to G all in the same formula, the only way I can think of doing it is to have an extra row which does the Sum(B3,B4,B5) part.

    Columns B to G are the same format, values will change but for the above example they can all be the same.
    The expected result from the formula would be 12.

    Any suggestions appreciated.

    Thanks

    I'm not understanding the 'hard part'

    Can you give more information and maybe an example workbook?
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  4. #4
    VBAX Tutor MINCUS1308's Avatar
    Joined
    Jun 2014
    Location
    UNDER MY DESK
    Posts
    254
    I think I understand what you want:
    help.png
    will the numerator always be cell b7?
    and should the answer always be in A10?
    am I only allowed to use cell a10 for the formula?
    - I HAVE NO IDEA WHAT I'M DOING

  5. #5
    VBAX Tutor MINCUS1308's Avatar
    Joined
    Jun 2014
    Location
    UNDER MY DESK
    Posts
    254
    help.jpg
    eh?
    there's a million ways to eat an apple this is just one
    - I HAVE NO IDEA WHAT I'M DOING

  6. #6
    VBAX Tutor MINCUS1308's Avatar
    Joined
    Jun 2014
    Location
    UNDER MY DESK
    Posts
    254
    sorry its a little blurry:
    = B7/SUM(B3:B5) + B7/SUM(C3:C5) + B7/SUM(D3: D5) + B7/SUM(E3:E5) + B7/SUM(F3:F5) + B7/SUM(G3:G5)

    if your numerator needs to progress B through G: just change the respective numerator from 'B7' to what ever the corresponding column letter is
    - I HAVE NO IDEA WHAT I'M DOING

Posting Permissions

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