+ Reply to Thread
Results 1 to 9 of 9

Need to Sum(Offset) cumulative values if possible

  1. 07-13-2022, 08:11 AM #1
    EvansB
    EvansB is offline
    Registered User
    Join Date
    09-14-2020
    Location
    London
    MS-Off Ver
    Office 365 Enterprise
    Posts
    88

    Need to Sum(Offset) cumulative values if possible

    Hi there,

    I have a sheet that presents values in 6 monthly periods and I would like to show same values annually (12 month period). I have managed to do it as per the formula on row 7 of the attached sample workbook. However, I used the same approach in row 8 in order to capture annually the cumulative amounts in row 4, however it didn't work, probably because the amounts are cumulative. It may be that another formula would be appropriate with respect to cumulative value.

    Any assistance would be appreciated.

    Regards

    Ben
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 07-13-2022, 08:12 AM #2
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2406 (Windows 11 23H2 64-bit)
    Posts
    81,812

    Re: Need to Sum(Offset) cumulative values if possible

    Are you still using Excel 2016?

    Please add manually calculated results into the yellow cells.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 07-13-2022, 08:16 AM #3
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2406 (Windows 11 23H2 64-bit)
    Posts
    81,812

    Re: Need to Sum(Offset) cumulative values if possible

    Guess - in C8 copied across:

    =SUM($C7:C7)
    Reply With QuoteRegister To Reply
  4. 07-13-2022, 08:39 AM #4
    EvansB
    EvansB is offline
    Registered User
    Join Date
    09-14-2020
    Location
    London
    MS-Off Ver
    Office 365 Enterprise
    Posts
    88

    Re: Need to Sum(Offset) cumulative values if possible

    Row 8 needs to pick up row 4. The difficulty being that row values in row 4 are for half-yearly periods and I'd like to transfer them to annual.
    Reply With QuoteRegister To Reply
  5. 07-13-2022, 08:40 AM #5
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2406 (Windows 11 23H2 64-bit)
    Posts
    81,812

    Re: Need to Sum(Offset) cumulative values if possible

    Please provide a workbook with manually calculated results. Thanks.

    Does this do what you want?

    In C8 copied across:

    =SUMIF($C$2:$L$2,C6,$C$4:$L$4)

    AliGW on MS365 Beta Channel (Windows 11) 64 bit

    B
    C
    D
    E
    F
    G
    6
    Sep-10
    Sep-11
    Sep-12
    Sep-13
    Sep-14
    7
    		 Annual Amounts
    1200
    1400
    1500
    1000
    800
    8
    		 Cumulative
    1200
    2600
    4100
    5100
    5900
    Sheet: Sheet1
    Last edited by AliGW; 07-13-2022 at 08:45 AM. Reason: Suggestion added.
    Reply With QuoteRegister To Reply
  6. 07-13-2022, 09:07 AM #6
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,426

    Re: Need to Sum(Offset) cumulative values if possible

    @Ali: what was wrong with =SUM($C$7:C7) ???
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
    Reply With QuoteRegister To Reply
  7. 07-13-2022, 09:08 AM #7
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2406 (Windows 11 23H2 64-bit)
    Posts
    81,812

    Re: Need to Sum(Offset) cumulative values if possible

    I understood that the OP was rejecting it with his response in post #4.
    Reply With QuoteRegister To Reply
  8. 07-13-2022, 09:33 AM #8
    EvansB
    EvansB is offline
    Registered User
    Join Date
    09-14-2020
    Location
    London
    MS-Off Ver
    Office 365 Enterprise
    Posts
    88

    Re: Need to Sum(Offset) cumulative values if possible

    Perfect! Thank you again.

    Ben
    Reply With QuoteRegister To Reply
  9. 07-13-2022, 09:34 AM #9
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2406 (Windows 11 23H2 64-bit)
    Posts
    81,812

    Re: Need to Sum(Offset) cumulative values if possible

    Glad to have helped.

    If you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.
    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. Enter cumulative values
    By RMCZ1967 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-12-2019, 11:34 AM
  2. [SOLVED] Cumulative sum (if values match)
    By Median in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-17-2018, 09:34 AM
  3. Cumulative Sum of values in an array
    By seigna in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-15-2013, 06:59 PM
  4. Cumulative Values
    By stevewilde in forum Excel General
    Replies: 2
    Last Post: 08-14-2009, 04:33 AM
  5. How to calculate cumulative values
    By Learner in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-11-2006, 02:35 AM
  6. Cumulative Values
    By David in forum Excel General
    Replies: 2
    Last Post: 08-04-2006, 06:55 PM
  7. sumif offset cumulative function question
    By barkiny in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-25-2005, 11:13 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1