Where to begin???
I tried this formula after changing the cells to make it fit my spreadsheet,
but it didn't work. It gave an "N/A" as the output.
"Spiky" wrote:
On Jan 2, 9:59 am, PointerMan
wrote:
To help clarify things, there are about 6700 parts, and they have varying
numbers of jobs that get done to them. Some have 8 jobs done on them, and
some have 50. I'm looking to sum up the amount of time it takes for each job
based on the times I set for them.
"PointerMan" wrote:
Part Jobs Total
A 10 5 25 5 220 999 ?
B 10 5 70 5 999 ?
C 10 5 60 220 999 ?
D 4 10 35 5 220 999 ?
E 5 70 100 20 5 220 ?
...
6700 rows deep x 50 columns wide
If Job 5 takes 1 day, Job 10 takes 1 day, Job 25 takes 3 days, etc what's
the easiest way to sum the total number of days for each part? I could do it
manually, but I don't want 50 sub-calculations in my formula.
On Jan 2, 9:59 am, PointerMan
wrote:
To help clarify things, there are about 6700 parts, and they have varying
numbers of jobs that get done to them. Some have 8 jobs done on them, and
some have 50. I'm looking to sum up the amount of time it takes for each job
based on the times I set for them.
Well, if the jobs always have the same time, based on job number, this
should work. Set up your jobs/times in a simple database somewhere,
say in Sheet2, A1:B100, sorted in numerical order. Start with a 0 - 0
job - days item. So:
0 0
5 1
10 1
25 3
Then this formula should work:
=SUMPRODUCT(B2:AY2,LOOKUP(B2:AY2,Sheet2!A1:B100))
|