+ Reply to Thread
Results 1 to 5 of 5

Can I create a range with absolute cell references and multiple sums?

  1. #1
    Registered User
    Join Date
    03-12-2014
    Location
    Weymouth, England
    MS-Off Ver
    Excel 2010
    Posts
    3

    Can I create a range with absolute cell references and multiple sums?

    The sum I currently have is:

    =SUM($C$4*C6+$D$4*D6+$E$4*E6+$F$4*F6)

    I'm looking for a way to insert a range so that I don't have to imput each individual sum.

    This spreadsheet will likely be hundreds of columns long by completion so I'm looking for a way to get from $C$4*C6 all the way to (for example) $Z$4*Z6. I haven't been able to come up with anything with my limited knowledge or ranges.

    Many thanks for reading.

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,597

    Re: Can I create a range with absolute cell references and multiple sums?

    Sumproduct might work for you but without seeing your details it is hard to tell.
    here is an example of how it might work...
    take this =B2*B3+C2*C3+D2*D3+E2*E3+F2*F3+G2*G3+H2*H3+I2*I3+J2*J3+K2*K3+L2*L3+M2*M3+N2*N3
    and replace with this
    =SUMPRODUCT($B$2:$N$2*B3:N3)
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Registered User
    Join Date
    03-12-2014
    Location
    Weymouth, England
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Can I create a range with absolute cell references and multiple sums?

    Capture1.PNG

    That doesn't seem to work unfortunately, thanks for trying though!

  4. #4
    Registered User
    Join Date
    03-12-2014
    Location
    Weymouth, England
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Can I create a range with absolute cell references and multiple sums?

    I should add that the top part is with SUMPRODUCT used, the bottom part is with the formula that I am currently using without ranges.

  5. #5
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,597

    Re: Can I create a range with absolute cell references and multiple sums?

    I think it would work based on your first post where you noted this... =SUM($C$4*C6+$D$4*D6+$E$4*E6+$F$4*F6)
    But based on your screen shot I'm not sure what you are striving for. What you placed in B6 is a modification of the formula I showed you but if what you want is in B14 and B15 those appear to be simple sums across the rows.
    Uploading a small spreadsheet with sample data and desired results could be a big help for you to get help.

+ 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. Copying Multiple Absolute Cell References
    By ShaneBell in forum Excel General
    Replies: 1
    Last Post: 02-12-2009, 04:04 PM
  2. How can I use non-absolute workbook/range references in formulas
    By rnabinger in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-19-2006, 12:10 PM
  3. [SOLVED] Changing cell references in a Range to Absolute
    By The Hawk in forum Excel General
    Replies: 1
    Last Post: 05-03-2006, 01:10 PM
  4. Replies: 5
    Last Post: 04-26-2005, 06:06 PM
  5. Multiple Absolute References
    By andibevan in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-10-2005, 07:28 AM

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