Need help with data referencing

[efoxa]

Ok this is a complicated one but here we go:

C:C on sheet 3 needs to return productivity based on the following

total time in G:G sheet 1, compared to estimated job time B:B sheet 2, based of specific related/matching data between A:A sheet 2 and J:J sheet 1

[NBVC]

Is Productivity a numeric column?

Assuming Productivity is on Sheet 2 is in column X

then:

=SUMIFS('Sheet1'!X:X,'Sheet1'!G:G,'Sheet2'!B2,'Sheet1'!J:J,'Sheet2'!A2)

assuming you are looking form each item in B2 and A2 of Sheet2, in columns G and J in Sheet1.

if this doesn't work, post a small sample workbook identifying the problem.

[efoxa]

i still dont think i completely understand, attached is a cop of my project.

so as you can see in J:J the issue is "not starting", and tech 645 (I:I) is working this job. the issue matches category in cell A4 sheet2. in cell B2 sheet3, you can see tech 645, and his "Productivity" is way to high. i need this to even out to compair issues (J:Jsheet1) with job category (A:Asheet2) according to tech (B:Bsheet3/I:Isheet3)

this is the hardest formula i have ever had to do, no matter what i have done i have not been able to figure it out.

[NBVC]

What is the expected result and why?..

[efoxa]

i need to show productivity for each tech. so if it take tech A 2.5hrs to perform a job that is est. @ 3hrs it will show that tech a (so far) has 150% productivity.

so when i input the issue in J:J sheet1, it will match up with job category ie. starting system in cell A6 sheet 2. with issue and time it took to complete job in J:J sheet 1 (issue) G:G sheet1. (total time)

if that makes sense?

[NBVC]

Ok, not sure if I understood 100%, but let's give this a try:

First, remove errors in column G by using formula:

Please Login or Register  to view this content.

copied down

then add a helper column in M to get the specified Est. Time for the category in J (which should match one of the entries in column A of Sheet2).

use formula in M2:

Please Login or Register  to view this content.

copied down.

then in Sheet3, C2:

Please Login or Register  to view this content.

which must be confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down.

[efoxa]

is this based off of a month period? becuase the %'s are not matching up, showing that time took = time est. =54%, should be 100%.

[NBVC]

I don't think then that I am following.

You will need to post a workbook with more examples of the main data and show the exact expected results with reasoning for the results.

I had assumed that one employee may work on several categories and that you need an average of those based on the estimated times.. I could have been wrong. You will need to precisely identify the issues.

P.s. Please do not PM questions. Start new threads.