Forumula Calculations

  • Thread starter Thread starter Amy
  • Start date Start date
A

Amy

I am trying to put together a real time budget so I know
where I am at all the time. Currently my first sheet in
my workbook consists of the total budget for each cost
code, the cost to date for the previous month per cost
code, and the remaining money in the budget per cost
code. On my second sheet I track the invoices that are
processed. My invoice sheet includes a date, cost, and
cost code. What I would like to do (if it is possible)
is to create a new column on my budget sheet with the
current monthly costs per cost code based on the
information I have on the sheet I am tracking my
invoices. I need to create a forumla on my budget sheet
that will calculate the total costs per cost code for the
month. My criteria on my invoice sheet would be that it
falls within certain dates and matches the specific cost
code in order to calculate on my budget sheet correctly
for each line I have.

Any help anyone can provide or tips would be greatly
appreciated.
 
Amy

Try this.

If sheet1 is named Budget with the Cost Code in
Column 'A'. Sheet2 is named Invoice, with the Invoice Cost
Code A1:A100 and the dollar amount in B1.B100 of the
Invoice sheet then enter in a new column on the Budget
sheet the following:

=SUMIF(Invoice!$A$1:$A$100,$A1,Invoice!$B$1:$B$100)

and copy down as needed.

I would suggets you enter several new columns on the
budget sheet named Jan, Feb, Mar....... and copy the
formula to each column.

I would also suggest adding additional Invoice sheets
named Jan, Feb, Mar....

Change the sheet reference in the formula to match the new
sheet. ie

=SUMIF('Jan Invoice'!$A$1:$A$100,$A1,'Jan Invoice'!
$B$1:$B$100)

=SUMIF('Feb Invoice'!$A$1:$A$100,$A1,'Feb Invoice'!
$B$1:$B$100)


Charlie O'Neill
 
Back
Top