Hello,
My company have a lot of employees who get all of the overhead allocate to them. However, we have a lot of contractors. They do not get employee benefits allocated to them. And also we have a lot of outside sales, they do not get facility expenses allocate to them.
I need 2 formulas.
One is sum all head count (from B2 to Q93) based on department and location (A 102).
Second formula I need is assign the cost to the head count.
Hope I explain clear.
Thank you for the help.
Jackson
Last edited by jackson_hollon; 12-16-2014 at 03:13 AM.
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and use the paperclip icon to open the upload window.
View Pic
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Glenn,
I have attached the sample here. Do not know how to explain. I stated what I need in the file. I really do not know which formula to use. Maybe SUMIF? I don't think VLOOKUP will work as there are too many criteria.
Thanks again for the help.
Jackson
maybe like this?
Glenn,
Thanks for the reply and the help.
The first formula is what I want. I am very grateful for the help.
Could you please take a look at the A157 for the formula 2? Thanks again for the help.
Jackson
I don't understand what you want. Where did 2.63 come from?What do you want to see, where & why??
Glenn,
I think I need 3 formula to accomplish what I need.
The $2.63 come from row 102 the benefit section. I need a formula that will look up the data in B102 ti Q115, and auto calculate the cost. For the 2.63, it is using shop 1 benefit $100, divided by the total head count of the month, multiple by the department 20 head count. Please look at the description next to 2.63. Shop 1 is the main office, they pay all of the benefit, therefore, the cost need to assign to all of the heard count in the benefit section.
The below IT section $55.96 and is based on each shop pays for the IT cost, but they are paying IT expenses for the entire company. Therefore, the cost should be total up, G150 plus G154 and allocate to each headcount. Please look at the G170, G168.
And the facility section on B178 to row 180, the cost is from row 151 and 155. The cost is based on each shop pays for their own cost, therefore, the cost is allocate to each headcount if they work for that shop.
Thanks again for the help.
Jackson
I don't understand the reasoning behind your desired answer. However, this delivers what you want (I hope). If not, please shout.
Glenn,
Thank you very much for the help.
It works. Thanks again.
Jackson
You're welcome & thanks for the reputation...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks