Replace multiple SumProduct/Vlookup with a dynamic array of conditions

Hi there,

I have tried to see if there was a similar post to my issue but are not able to find something that match what I am trying to do.

I have been assigned to come up with a roster and I'd love to help the one from someone that feels comfortable with it. I am working for an NGO so resources are quite limited, thus me an HR person doing the roster for medical people

Anyway I'd like to minimize a formula I am using to calculate the total number of shift in my roster for every person that work.
I am using a mix of SumProduct and Vlookup in the formula to calculate the total. The idea behind it is to make it as simple as possible, which is far to be the case now.

Below my two tables:

TABLE 1 > A1:F7
Total
Bob D1 D2 D2 D2 7
Kyle D3 D3 D1 D1 8

TABLE 2> A5:B7
D1 1
D2 2
D3 3


Formula in F7:
=SUMPRODUCT(--(B2:E2="D1")*VLOOKUP("D1",$A$5:$B$7,2,FALSE))+SUMPRODUCT(--(B2:E2="D2")*VLOOKUP("D2",$A$5:$B$7,2,FALSE))+SUMPRODUCT(--(B2:E2="D3")*VLOOKUP("D3",$A$5:$B$7,2,FALSE))

Problem
As you can see the

Faboolus,

Can you attach the worksheet, even if you show just a few rows with "dummy" data, so we can follow the logic, as your references seem to overlap (e.g. A5 (Table 2) is in the Table 1 range A12:F7)?

Ochimus

Sorry I'll delete this thread I posted it twice.

That's the one. I'll attach an Excel sheet to it but connection is not really good
http://www.excelforum.com/excel-form...onditions.html