Summing multiple departments

[Campbell]

Hi there,

I have a problem where I have to report from a central database the
department and cost centres of each. the reporting format that I require is
like this:


Head Office
Actual Budget Varience
Accounts
Legal
HR

Basingstoke
Actual Budget
Varience
Accounts
Legal
HR

Plymouth
Actual Budget
Varience
Accounts
Legal
HR

etc etc

the Budget and Varences are already filled from existing information.
However I need to draw from a database the seperate Cost Centre costs by
Department and the database looks like:

DATE, DEPARTMENT,COSTCENTRE,ACTUAL e.g.
12/10/08, Basingstoke, Accounts, 1200
13/10/08, Head Office, Accounts, 500
14/10/08, Basingstoke, HR, 250

And I need to sum the "Actuals" into the relevant Dept Cost centres as
above. I can do it with a Pivot table but then it doesn't match to the
Budgets already there.

Does anyone have an idea what I am talking about and if so can you help?

Cheers

Cam

 

[Sean Timmons]

Sounds like you want a SUMPRODUCT

So, for Basingstoke Accounts, let's assume the name Basingstoke is in cell
A7 and Accounts is in A10.
Let's also assume your data is in Sheet2 and values are in A - D

you can do..

=SUMPRODUCT(--(Sheet2!B2:B10000=A7),--(Sheet2!C2:C10000=A10),D210000)

Which will sum all actuals where Center is Basingstoke and CostCentre is
accounts.

Should be able to get all your other actuals by just tweaking the above
formula to fit your actual cells.