+ Reply to Thread
Results 1 to 3 of 3

Sum formula to exclude monthly budget figure when actual figure is entered

  1. #1
    Registered User
    Join Date
    02-01-2013
    Location
    Wellington, New Zealand
    MS-Off Ver
    Excel 2007
    Posts
    2

    Sum formula to exclude monthly budget figure when actual figure is entered

    Hi - I am an excel novice - and need a formula which will add up the monthly figures in a budget.
    That is - at the beginning of the financial year, the total will be the 12 months of budgeted expenditure. However as the year progresses, and an 'actual' expenditure figure is added in month 1, this needs to be included instead of the budgeted figure.

    I have created a very long and messy formula which works, but I am keen to learn, and know there must be a tidier and simpler way to do it. I have played around with SUMIFS with no joy.

    This is my long formula. I can imagine your laughter!! A2, C2, E2 etc are actual expenditure figures, B2, D2, F2 figures are budgeted expenditure.

    =IF(A2>0,-B2)+IF(C2>0,-D2)+IF(E2>0,-F2)+IF(G2>0,-H2)+IF(I2>0,-J2)+IF(K2>0,-L2)+IF(M2>0,-N2)+IF(O2>0,-P2)+IF(Q2>0,-R2)+IF(S2>0,-T2)+IF(U2>0,-V2)+IF(W2>0,-X2)+SUM(A2:X2)

    Thanks so much for any ideas.

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,446

    Re: Sum formula to exclude monthly budget figure when actual figure is entered

    Perhaps post a sample sheet showing what you have and what you need

  3. #3
    Registered User
    Join Date
    02-01-2013
    Location
    Wellington, New Zealand
    MS-Off Ver
    Excel 2007
    Posts
    2

    Smile Sample sheet attached

    Sure. Sample attached. Thanks again for any help - I appreciate it.Budget versus actual.xlsx

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1