I have a spreadsheet that tries to calculate the total time that needs to be spent completing a large set of jobs.

Each job has a number of sub-categories, but it's a fixed list of just a few sub-categories. (In other words the sub-categories are the same for each job.)

Each job also has specific people assigned to it, as well as a couple of assistants who do specific types of specialized tasks.

The list of workers is a pretty small finite list of only six fixed unchanging names. But the job list (the names of each of the jobs) is fairly large. So everyone does and/or assists with more than one job.

Each job may have only one person, or two persons, or three persons working on that job.

Spreadsheet looks like this:



Now, it's easy to simply sum the total number of hours for the entire project, for one job, or for one sub-area of a job. So I can easily see how long the entire project will take.

But what I want to do is distribute the workload by entering different names into the fields for the job captain and for his assistants.

I have already figured out how to sum the number of times that a given worker appears in the list, and display a count of how many jobs or assisting-jobs a given worker got assigned:

That gets me an accurate count of how many times someone got assigned a job in the list (provided I've typed the name accurately in both places). So I can balance it that way. Kind of. I have a spot off to the side of the spreadsheet with six names and six totals next to those names, counting up how many times I put them in that list.

That's nice except different jobs and sub-categories of the jobs take different amounts of hours. What I really want, and what my brain can't wrap around, and what I can't figure out how to google, is:

How many hours does each person end up spending? Calculated in such a way that I can keep shuffling the names around a bit and distribute the HOURS, as well as the number of jobs.

I'm not just assigning it willy-nilly because people have differing skill sets, and some people have expressed either an interest or a disinterest in some specific area.

I want a formula that says...

"If Fred's name is in one of the right hand columns, then sum the HOURS from the middle columns, and add that sum to Fred's grand total of hours."

Of course, because more than one name might appear in the right hand columns, then you'll need to average the hours amongst the people involved. For instance:

"If Fred and Jane's names are in one of the right hand columns, then sum the hours from the middle columns, and give half of those hours to Fred's grand total and half of those hours to Jane's grand total"

To throw a final wrench into the works, the formula must tolerate blank cells. For instance, if one of the Hours fields is blank, then it must not mess up the summing of the hours for that row, if one of the name fields is blank, then it must not cause the averaging algorithm to fail, etc.

Sounds really complicated! Hm.

Any ideas?

So stated differently:

Person gets a prorated share of the hours, from the sum of the hours listed in the three middle columns, if his name in in one of the right hand columns. Prorated share to be contingent on how many others share the work for that task.

Edited next:
You need additional columns, one for each individual.
In that column generate a formula that calculates the hours that person receives based on the sum of hours divided by the number of workers sharing that task, but only if he is one of the workers for that task.
Copy formula across and down. Formula should reference name at top of column when comparing against names to left. That way the formula is generic and works for all cells in new columns.

sum workers total at bottom of new columns.




Isn't this what ms-project was created for?

Code:

NameOfJob       Subcat1 Subcat2 Subcat3 JobCaptain JobAssistant1 JobAssistant2           bob     sam     jerry   cindy   jane

(Jobname1)      3       3       3       bob                                               9 
(Jobname2)      65      4       32      sam                       cindy                          50.5            50.5
(Jobname3)      4       34      21      jerry      bob            jane                   19.66           19.66           19.66
(Jobname3)      4       34      21      jerry                                                            59
(Jobname3)      4       34      21      jerry                                                            59
                                                                                         28.67   50.50  137.67   50.50   19.67

        cell  i6  >>>   IFERROR(IF(SEARCH(I$4,($E6&$F6&$G6)),SUM($B6:$D6)/COUNTA($E6:$G6),""),"")

cell i6 in this example is the cell containing 9 just under bob. That formula is copied throughout the cells in the tally columns.

That worked!

I was hoping for a more elegant way involving a big nested forumula without needing the extra spreadsheet columns, but your method works great and was very quick to implement.

Thanks very much!

:-)

Interesting minor bug in the formula:

If someone is his own assistant (in other words, the same name shows up twice in the same row), then the total sum for that person is off.

I know the idea of someone being his own assistant is weird, but the actual things I'm putting into the spreadsheet sometimes means that'll happen. (Sorry I didn't bring this up before).

I can work around it for now, but do you have an idea of how to fix the formula in "i6" so that it'll work even if a name is listed twice?

=SUM($B6:$D6)*(IF(I$4=$E6,1,0)+IF(I$4=$F6,1,0)+IF(I$4=$G6,1,0))/COUNTA($E6:$G6)

And yes, unless you are trying to split the work other than equally having one's self as an assistant makes no sense.

=SUM($B6:$D6)*COUNTIF($E6:$G6,I$4)/COUNTA($E6:$G6)

Shorter but the same result. COUNTIF what I wanted to use before but wasn't finding it in the function list.