+ Reply to Thread
Results 1 to 7 of 7

Cumulative addition on montlly P & L sheet

Hybrid View

  1. #1
    Registered User
    Join Date
    12-26-2011
    Location
    Shropshire; UK
    MS-Off Ver
    365 Excel for Mac V16.28
    Posts
    28

    Cumulative addition on montlly P & L sheet

    I currently use a standard monthly profit & loss report which gives the monthly totals and cumulative totals as the year progresses.
    I would like to know if there is a way I can produce 'automatic' sums for year to date figures as the year progresses without having to resort to putting totals in manually i.e. say:
    A Jan Budget
    B Jan Actual
    c Feb Budget
    D Feb Actual
    etc. to
    Y Budget YTD (i.e. after March; Jan + Feb + March are automatically summed to Y)
    Z Actual YTD (i.e. after March; Jan + Feb + March are automatically summed to Y)

    Thanks in advance for any suggestions.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Cumulative addition on montlly P & L sheet

    Based on your version and suspected layout where we assume A1 says Jan Budget, B1 says Jan Actual etc and row 2 contains values where Budget is added in advance and Actuals as created...

    YTD Budget
    Y2:
    =SUMIFS($B2:$X2,$B$1:$X$1,"*Budget",$A2:$W2,">-9.99E+307")
    
    YTD Actual
    Z2:
    =SUMIF($A$1:$W$1,"*Actual",$A2:$W2)
    To clarify above...
    Budget YTD sums all values where header says "something Budget" and where there exists an accompanying Actual value.
    Actual YTD sums all values where header says "something Actual"
    Last edited by DonkeyOte; 12-26-2011 at 04:52 PM. Reason: typo

  3. #3
    Registered User
    Join Date
    12-26-2011
    Location
    Shropshire; UK
    MS-Off Ver
    365 Excel for Mac V16.28
    Posts
    28

    Re: Cumulative addition on montlly P & L sheet

    Thanks for that DonkeyOte, just what I was looking for.
    Just one thing though, having tried the example, 'Jan Budget' value at A2 seems to be missing from the cumulative total?

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Cumulative addition on montlly P & L sheet

    My mistake - I reversed the range references incorrectly... based on budget, actual, budget, actual etc... with Jan Budget being A1

    YTD Budget
    Y2:
    =SUMIFS($A2:$W2,$A$1:$W$1,"*Budget",$B2:$X2,">-9.99E+307")
    
    YTD Actual
    Z2:
    =SUMIF($B$1:$X$1,"*Actual",$B2:$X2)

  5. #5
    Registered User
    Join Date
    12-26-2011
    Location
    Shropshire; UK
    MS-Off Ver
    365 Excel for Mac V16.28
    Posts
    28

    Re: Cumulative addition on montlly P & L sheet

    Thanks again DonkeyOte, I'm progressing, but can't seem to get the budget side to work correctly. I have attached a sample of what I have - maybe you can figure what I've done wrong?
    Sample P&L.xlsx

    Thanks in advance (hopefully) I'm really grateful for your help.

  6. #6
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Cumulative addition on montlly P & L sheet

    You have a trailing space after Budget (ie "Budget ") so either:

    =SUMIFS($C3:$Y3,$C$2:$Y$2,"Budget*",$D3:$Z3,">-9.99E+307")
    else, remove both trailing space from headers and wildcard from SUMIFS

    =SUMIFS($C3:$Y3,$C$2:$Y$2,"Budget",$D3:$Z3,">-9.99E+307")

  7. #7
    Registered User
    Join Date
    12-26-2011
    Location
    Shropshire; UK
    MS-Off Ver
    365 Excel for Mac V16.28
    Posts
    28

    Re: Cumulative addition on montlly P & L sheet

    Once again thanks, much appreciate your time.

+ 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