+ Reply to Thread
Results 1 to 5 of 5

Sum across columns

  1. #1
    Registered User
    Join Date
    11-06-2007
    Posts
    48

    Sum across columns

    i have a data table laid out in the following manner;

    ColA ColB ColC ColD ColE ColF
    Product Desc Ohio Georgia NewYork Indiana
    X A 5 4 3 4
    Y B 3 2 5 1
    Z C 6 3 6 2


    I want to create a sumtotal in ColG from ColC,ColD,ColE and ColF but all vaules in ColC have to be multiplied by a factor of 2, colD by 3, colE by 1.5 and colF by 3.5.

    This list is dynamic and can grow by rows and columns. The factor by which a state is multiplied will always be the same for the state. Essentially, the factor for all states will be coded and will never change.

    How can i loop through all the col's with state names, evaluate the name, apply the factor and sum the result for every state in colG.

    this what i use to just sum the data by product

    Please Login or Register  to view this content.
    But i also need to need to add the factors based on the state name and sum all that as well. Any ideas on how to get there will be helpfull.

    Thanks

  2. #2
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,607
    Does this work? See attachment.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  3. #3
    Valued Forum Contributor rwgrietveld's Avatar
    Join Date
    09-02-2008
    Location
    Netherlands
    MS-Off Ver
    XL 2007 / XL 2010
    Posts
    1,671

    re:complicated Sum accross columns

    maybe this is not the answer you expect but I would like to give you another view.
    I've defined dynamic names for your states
    Please Login or Register  to view this content.
    and then it becomes a "simple" SUM
    Please Login or Register  to view this content.
    I like to avoid code as far as possible for all the obvious reasons...
    Looking for great solutions but hate waiting?
    Seach this Forum through Google

    www.Google.com
    (e.g. +multiple +IF site:excelforum.com/excel-general/ )

    www.Google.com
    (e.g. +fill +combobox site:excelforum.com/excel-programming/ )

    Ave,
    Ricardo

  4. #4
    Registered User
    Join Date
    11-06-2007
    Posts
    48
    Thank you very much. Both solutions will work, but in this particular case i can't reveal formulas in the sheet and it has to be done in code. I adapted StephenR's solution with slight modifications to suit how my sheet is laid out.

    Thanks again

  5. #5
    Valued Forum Contributor rwgrietveld's Avatar
    Join Date
    09-02-2008
    Location
    Netherlands
    MS-Off Ver
    XL 2007 / XL 2010
    Posts
    1,671

    re:Sum across columns

    There is always the posibility to HIDE your formula's

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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