• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Not sure which formula to use

T

tanner24

New Member
Am still learning a bit on excel, so appreciate your help. I have 15 employees, each working on numerous projects. I want to see what % of time they are spending on each project, for each employee, from July-Nov. I had just used a list of employee names (let's just say in Column C) in rows 1-15 and then used SUMIF to reference the total hours for each employee (SUMIF name in range was equal to "C1," give me total hours across all projects, then C2, etc).


Then for every project they worked on, I used IF statement to say that, IF the name of the employee (in Column A) equaled the name in my list (C1:C15, row 1, row 2 etc), give me total hours per project divided by their total hours. I repeated that for each employee/project. But that meant I had to change the cell references in my IF statement for each employee.


Is there an easier way to do this? I had thought perhaps INDEX & SUMPRODUCT somehow, but I'm not sure if this is correct, or how to structure the formula. I hope this makes sense.


Thank you!!
 
Hi ,


SUMIFS can be used if you have multiple criteria. So can SUMPRODUCT.


Can you give more details about how your data is organized ? C1:C15 contains the names of your 15 employees. What about the number of projects , and the hours that each employee clocks on each of his / her projects ?


Narayan
 
sure, thank you. I have not used SUMIFS before. The data is imported from monthly timesheets, so:

Column A is the employee name (from the timesheet)

Column B is the project

Column C-G are the monthly hours (Column C is July, D is August, E is Sept, etc)

Column H is the Total Hours from July - Nov

so the employee name, project, hours etc repeat on successive rows (say there's 100 rows)


I added a column I to this data for the "% per employee" where the Total Hours (Column H) per project (rows 1-100) was divided by Column J (my list of employee names in rows 1-15 using SUMIF).


I was trying to make it so that the hours an employee worked across all projects would be 100% (per employee), and then the Total Hours per project was a % of that. That's why I had to keep changing the cell references...but...surely there is an easier way!


Thanks for your help!
 
oh, also the number of projects can vary - depending on the employee, as will the hours worked. Ultimately this is a question of resourcing, as I'm having trouble with employees spending too much time on projects that don't pay enough to cover their time. So I want something I can analyze showing how they are actually spending their time across projects. One person might work on five projects, another might work on fifteen.
 
Hi ,


Can you confirm one point ?


In each row , for a particular employee , and for a particular project , will there be entries in multiple months ? Or will each row have only one number in one particular month ?


Narayan
 
The length of each project varies, so, some rows may have hours posted in each month, and several employees could be working on the same project (but not always at the same time), whereas another project may only have lasted a month. Does this help?
 
Hi ,


I'll tell you what I tried out :


Data is in the range A6:G13 ; row 5 has the headers as follows : "Employee Name" "Project ID" "July" "August" "September" "October" "November" "Total"


The data from cells A6 through G13 is as follows :


A ...... 1102 ... 17

B ...... 1102 ... ... 13 ... 22

A ...... 1102 ... ... .. ... 33

A ...... 1103 7 ... 12

A ...... 1102 ... ... .. ... 32

B ...... 1102 ... ... .. ... .. ... 77

B ...... 1104 ... 12

A ...... 1103 ... ... .. ... 39


Using the following formula in column H gives the total hours worked by each employees ( across all projects )


=SUMPRODUCT((($A$6:$A$13=A6)*($B$6:$B$13=B6))*($C$6:$G$13))


Enter this formula in H6 , and copy it down to H7 , H8 ,...


Narayan
 
You must log in or register to reply here.
Back
Top