+ Reply to Thread
Results 1 to 8 of 8

SUM formula - change range with value input

  1. 12-30-2022, 05:48 AM #1
    jncswe
    jncswe is offline
    Registered User
    Join Date
    01-28-2010
    Location
    Sweden
    MS-Off Ver
    Excel 2007
    Posts
    20

    Lightbulb SUM formula - change range with value input

    Can someone help me to figure out how I should right below formula?

    "Smarter" version of a regular SUM(A1:A10) - I want too be able to input the value 10 (then 11 and 12 etc) in the range.

    =SUM(B7:INDIRECT("B"&AD4)) - if i copy this the "B" obviously do not change.
    =SUM(C7:INDIRECT("C"&AD4))
    =SUM(D7:INDIRECT("D"&AD4))
    =SUM(E7:INDIRECT("E"&AD4))
    etc
    etc
    ....
    ....
    Reply With QuoteRegister To Reply
  2. 12-30-2022, 06:00 AM #2
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2312
    Posts
    42,610

    Re: SUM formula - change range with value input

    I cannot visualise what you want. Please SHOW us on a sample Excel sheet. The yellow banner (top) tells you how to attach sample files.
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
    Reply With QuoteRegister To Reply
  3. 01-02-2023, 03:16 AM #3
    jncswe
    jncswe is offline
    Registered User
    Join Date
    01-28-2010
    Location
    Sweden
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: SUM formula - change range with value input

    Hi Glenn,

    Apologise, the logic in above text wasnt there. Hence it is hard to visualise. Please see attached excel and image.

    Attachment 811551

    Attachment 811552
    Attached Images Attached Images
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 01-02-2023, 04:06 AM #4
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    42,949

    Re: SUM formula - change range with value input

    One way:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  5. 01-02-2023, 04:10 AM #5
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    42,949

    Re: SUM formula - change range with value input

    And:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    copied down
    Reply With QuoteRegister To Reply
  6. 01-02-2023, 04:11 AM #6
    josephteh
    josephteh is offline
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    2,645

    Re: SUM formula - change range with value input

    Try,
    H2=SUM(INDIRECT(CELL("address",A2)&":"&G2)), copy down
    F8=SUM(INDIRECT(CELL("address",A8)&":"&$F$7&ROW(A8))), copy down
    Reply With QuoteRegister To Reply
  7. 01-02-2023, 04:19 AM #7
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    42,949

    Re: SUM formula - change range with value input

    Or maybe
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    copied down
    Reply With QuoteRegister To Reply
  8. 01-02-2023, 04:32 AM #8
    josephteh
    josephteh is offline
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    2,645

    Re: SUM formula - change range with value input

    H2=SUM(A2:INDIRECT(G2)), per TMS
    F8=SUM(A8:INDIRECT($F$7&ROW(A8)))
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Countifs range should change dynamically with user input
    By abuharvey in forum Excel General
    Replies: 5
    Last Post: 04-10-2020, 03:50 AM
  2. [SOLVED] Worksheet Change? Use a formula cell as input and then revert back to original formula...
    By trickyricky in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-01-2016, 11:39 PM
  3. Defining an extra range to execute a different input change
    By ScottyDo in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-07-2012, 02:19 PM
  4. [SOLVED] Change event to Add or update comment with input box when cell in a range is changed
    By fratello in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-29-2012, 06:40 AM
  5. Change input range of 50+ drop downs
    By Kazstankgardner in forum Excel General
    Replies: 3
    Last Post: 06-19-2012, 07:54 AM
  6. [SOLVED] Result of formula doesn't change when I change input (the value in a cell)
    By Motox in forum Excel General
    Replies: 2
    Last Post: 06-11-2012, 07:30 PM
  7. Custom change Macro's Range value, from cells input.
    By Kenji in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-17-2009, 11:54 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1