Excel Help

[stigal]

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!

 

[XL Guru]

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

 

[XL Guru]

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