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:
NameOfJob Subcat1 Subcat2 Subcat3 JobCaptain JobAssistant1 JobAssistant2
(Jobname1) (hours) (hours) (hours) (Name1) (Name2)
(Jobname2) (hours) (hours) (hours) (Name3)
(Jobname3) (hours) (hours) (hours) (Name4) (Name1) (Name5)
...
...
...
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:
{=SUM(IF((RangeOfWhereIPutInWorkerNames="Name3",1,0))}
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?