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 xld's Avatar
    Joined
    Apr 2005
    Posts
    24,836
    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
    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

    ------------------------------------------------------------------------------------------------------------------------
    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)
    (multiple files can be selected while holding Ctrl key) / 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
    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
    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
    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
  •