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:44*am, 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.

How do you know how many days per job? And what are all those numbers/
columns you posted?

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.

"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))

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))

On Jan 2, 11:09*am, PointerMan
wrote:
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.


Why don't you post what you used, exactly. Including the location of
the database of jobs/times. And check that the database is sorted
ascending.

Hi,

Why don't you post a same with the results you want. As it is we can't tell
what the times are. For example, is 5, 25, 5, 220, 999 in seconds, or
minutes, or hours or...?

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"PointerMan" wrote:

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))

Actually, I just realized I did that formula wrong, anyway. You only
wanted the sum of the days. Take out the first argument, it should
just be this:
=SUMPRODUCT(LOOKUP(B2:AY2,Sheet2!A1:B100))

If you got an N/A error, it probably means the database was incorrect.
It returns N/A with a bad Lookup.

I assumed that 5, 1, 10, 999, 220 were all job names. Was that correct?

Spiky,

That did the trick. Thanks!



"Spiky" wrote:

Actually, I just realized I did that formula wrong, anyway. You only
wanted the sum of the days. Take out the first argument, it should
just be this:
=SUMPRODUCT(LOOKUP(B2:AY2,Sheet2!A1:B100))

If you got an N/A error, it probably means the database was incorrect.
It returns N/A with a bad Lookup.

I assumed that 5, 1, 10, 999, 220 were all job names. Was that correct?