formula and automatic calculation

[Aboiy Del Rio]

I would like to request your assistance if you could give
a formula for this calculation.


Sept Budget: 45,000 Engineering
25,000 Administration
3,000 Human Resources
1,500 Procurement

Expenses : 35,070 Engineering
27,750 Administration
25,300 Engineering
23,000 Engineering
75 Human Resources
45 Procurement

1 - Total : ( for expenditure for each dept.)

2 - Over Expenses : ( amount excess from the budget for
each dept.)


From the above you can see there is a specific budget for
a particular dept. Now my problem is that i need to have a
formula that would breakdown and compute the various
expenses of each dept. giving their total expenses for the
current month as well as the excess amount from their
budget.

Is there anyone who is kind enough to lend a helping hand.

Thanks and regards to all the excel newsgroup.

aboiy

 

[Anon]

I would like to request your assistance if you could give
a formula for this calculation.


Sept Budget: 45,000 Engineering
25,000 Administration
3,000 Human Resources
1,500 Procurement

Expenses : 35,070 Engineering
27,750 Administration
25,300 Engineering
23,000 Engineering
75 Human Resources
45 Procurement

1 - Total : ( for expenditure for each dept.)

2 - Over Expenses : ( amount excess from the budget for
each dept.)


From the above you can see there is a specific budget for
a particular dept. Now my problem is that i need to have a
formula that would breakdown and compute the various
expenses of each dept. giving their total expenses for the
current month as well as the excess amount from their
budget.

Is there anyone who is kind enough to lend a helping hand.

Thanks and regards to all the excel newsgroup.

aboiy

Formulas such as
=SUMIF(B1:B10,"Engineering",A1:A10)
(adjust ranges to suit your data) will allow you to add up the expenses for
each department.

Simple difference formulas such as
=D1-C1
(where the department budget figure is in C1 and the department total
formula is in D1) will give the overspend.

 

[Aboiy Del Rio]

Hi Anon,

Im not quite familiar in using complicated formula, but
can you explain a little further to me the formula you've
just given : =SUMIF(B1:B10,"Engineering",A1:A10)

Actually, its a day to day entry wherein they put in one
column assuming "B column" all the figures and in "C
column" the particular dept.,other succeeding columns are
just particulars. Figures are in small amounts. Now if I
consider B1:B10 as the figure how it was able to know the
specific dept. if it is located in another column.

Im a bit confuse.

For your kind assistance.

aboiy