| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing multiple departments
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Summing multiple departments
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),D2:D10000) 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. "Campbell" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Summing across multiple worksheets | Excel Worksheet Functions | |||
| About IF and summing IF over multiple spreadsheets | Excel Discussion (Misc queries) | |||
| Departments in one cell | Excel Discussion (Misc queries) | |||
| Would like a Template to conduct process review for departments | Excel Discussion (Misc queries) | |||
| How to monitor productivity within departments using Microsoft Exc | Excel Worksheet Functions | |||
Linear Mode
