Hi guys,
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:F3
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 formula is already quite long and I must admit pretty messy. Problem is if my table becomes 15 rows I'll have to add everything manually.
Would it be possible to have all conditions dynamic so that I don't need to enter any condition manually as it is currently the case, e.g. "D1", "D2", "D3"?
Not sure if that makes sense to any of you but would definitely appreciate some wisdom.
Thanks a lot,
Fab
Bookmarks