1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Help

Discussion in 'Business Applications' started by stigal, Apr 15, 2004.

Thread Status:
Not open for further replies.
  1. stigal

    stigal Thread Starter

    Joined:
    Feb 11, 2003
    Messages:
    10
    I have to do my department budget in Excel. I am using formulas to get the totals for the weeks and putting that information in a separate sheet. For example:
    April 8
    60840-75 5000.00
    60840-55 7000.00

    April 15
    60840-75 10000.00
    60840-55 10000.00
    (this is a running total that keeps calculating each month)
    These sheets are totals and I am wondering if there is anyway to lock out April 8 so that it will not continue to calculate the changes. I want to know as of April 8 how much I spent, April 15 etc. Can this be done?

    Thanks for your help!
     
  2. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    stigal,

    >> any way to lock out April 8

    I don't get what you mean by this. Plus I've never dealt with budgets. Those points aside, my gut feeling is your data layout doesn't lend itself to analysis.

    If it was in a consisent format, you could use SUMIF to give you a running total (per budget code) for each spend, as per the picture (D2 is selected so you can see its formula in the formula bar, you would just copy this down column D for new entries).

    Or you could use SUMPRODUCT to query the data in a separate table (as per F1:H3 ; the formula in H2 is

    =SUMPRODUCT(($A$2:$A$1000<=$G$1)*($B$2:$B$1000=G2)*$C$2:$C$1000)

    ).

    HTH,
    Andy
     

    Attached Files:

  3. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    Doh! Apologies stigal ; once again I get full marks for misunderstanding the question (the data you listed is the summary sheet and not the source data).

    Notwithstanding, SUMPRODUCT can do the job (summing by 2 criteria, date and budget code).

    In the attached, I've guessed at your source data layout (Sheet1), and added text versions of the summary sheet formulas (Sheet2, column B).

    Note the (e.g.) "<=$A$1" section in the formula, the key to summing up to a required date. Translated, the formula in B2 (text version in C2) says sum everything in Sheet1!C2:C1000 where

    (a) Sheet1!A2:A1000 is less than or equal to Sheet2!A1 (the date 8-Apr)

    and

    (b) Sheet1!B2:B1000 equals Sheet2!A2 (the budget code 60840-75).

    I've added some boxes & lines on the pic to try and make the formula references clearer. Your problem however is going to be referencing the date "heading" in your summary sheet. To get some idea of why, see Excel Help for absolute vs relative references, then post back if you need more help.

    Rgds,
    Andy
     

    Attached Files:

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/220649

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice