Help with Excel Forrmula

Hi,

I need to seek for guidence for excel for excel formula. My predecessor is using some complex formula which I could not get it to work. He was using a mixture of ANDs, IF and SUMIFS.

long story short, we have different offices globally. Each office will incur cost that needs to be allocated to the departments that are operating in their location.

Attached is the staff information that I received in order to calculate the percentage that each department should receive for different costs.

Example:

Office General Cost - Distribute evenly by headcount of staff working in that office BUT do not include Admin staff. This is because Admin staff is part of office general cost.

Electricity - Distribute 80% of electricity for each office to Marketing staff and 20% to the rest of the staff based on headcount BUT do not include admin staff.

What will be the most efficient formula such that everytime I need to update this information, I can just copy and paste the formula all the way down to the last staff. We have over 3000 staff worldwide and so many offices to "allocate cost". The above is just ONE of the many costs that such complexity judgement.

I would format your data as a table (based on your sample, highlight B1:F14 > Insert > Table)

Then, put the total electric bill in another cell (I chose I2).

Now create a column to show the allocated electricity cost per employee (I put this in column G) using this formula:

G2 =IF([@[Business Group]]<>"Admin",IF([@[Business Group]]="Marketing",(I$2*0.8)/COUNTIF([Business Group],"Marketing"),(I$2*0.2)/COUNTIFS([Business Group],"<>Admin",[Business Group],"<>Marketing")),"")

With all of this in place, all that you have to do is fill in the employee data (columns B through F and the electricity cost will update automatically. If you want to change the total electric cost, simply change the value in I2.