Issue with merging 2 caclulations to look up and sum

Hi Dhinesh,

I would use another sheet (StatusPoints) that contains the points table based on the table in Status sheet using the vlookup function. Then sum the points for each employee. Than you can use the lookup function again in the Employee sheet - see attached.

Regards, Ralf.

Hello Ralf, Thank you for your time!

I m afraid I can not have another tab like the way advised.

Many thanks!
Dhinesh.

Manual way is also possible, but more complicated and less flexible:
Content of B2 in Sheet Employee:
=IF(VLOOKUP(A2,Status!A:E,2,0)="Passed",VLOOKUP(INDEX(Status!$1:$1,1,2),Metric!A:B,2,0),0)+IF(VLOOKUP(A2,Status!A:E,3,0)="Passed",VLOOKUP(INDEX(Status!$1:$1,1,3),Metric!A:B,2,0),0)+IF(VLOOKUP(A2,Status!A:E,4,0)="Passed",VLOOKUP(INDEX(Status!$1:$1,1,4),Metric!A:B,2,0),0)+IF(VLOOKUP(A2,Status!A:E,5,0)="Passed",VLOOKUP(INDEX(Status!$1:$1,1,5),Metric!A:B,2,0),0)

Regards, Ralf.

Thank you very Much! that is just great... This way I can learn little bit more about the functions used.
Very kind.

Thank you Very Much Ralf... Very Kind of you!

Hello Sir, I was too early... Just testing it now Properly...

formula Seems to be less flexible. It does not know the course names and It does not know the ones that employee has passed.
Ex: in the metric tab, If I change course name to the course that employee has not passed, Ex: Course Z, System still calculates the points.

We need it to work dynamically..which checks for courses that employee has passed against the courses maintained in metric tab.

are you able to help please?

Try

in B2

=SUMPRODUCT((INDEX(Status!$B$2:$E$5,MATCH($A2,Status!$A$2:$A$5,0),0)="Passed")*(Status!$B$1:$E$1=Metric!$A$2:$A$5)*(Metric!$B$2:$B$5))

It has done the job... thank you very very much. saved my weekend.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.