Monthly schedule sum letter values

So
I have to create a monthly work schedule where the work hours and work time vary
So I created a spreadsheet "legend" where in 1 column are letters and column next to it has hour values
and other spreadsheet(s) are where those letters should be used and summed to keep count when doing next month schedule that how much all the workers has hours planned
I have tryd to counting, vlookup, suming.. but either some mistakes in my trys or im on a wrong path to do that.

Iv included a file with the legend and one spreadsheet =)

Thanks in advance.. Mamm0th.

Try this array formula entered with CTRL + SHIFT + ENTER

=SUM(IF(COUNTIF(Legend!$D$3:$D$27,D3:AG3),VLOOKUP(D3:AG3,Legend!$D$3:$E$27,2,0),0))

It didnt count, it shows that the value is 0.

It's an array formula that requires special keystroke to enter it.

After typing the formula, don't press enter.
Instead, Press CTRL + SHIFT + ENTER
When entered correctly, the formula will be enclosed in {brackets}

Aha.. got it working for one row.. but i cant copy it down for other employees hourcounting..
on next employee row it just shows #N/A. Can u hint me with that also?

Yes, after further testing it was only working based on the first letter value in the row..

If you sort the data on the Legend Page in Ascending order by column D
Try this instead (still array entered)

=SUM(IF(COUNTIF(Legend!$D$3:$D$27,D3:AG3),LOOKUP(D3:AG3,Legend!$D$3:$E$27),0))

Aha.. after i sorted to ascending, then ur new formula worked for all.. About this topic we could close it.
But I have more questions
I am just trying to make a plank document another person could work on.
But there are a few stones he could stumble upon if he uses this excel file.
There will be many sheets with many workers.
So here are the questions:
1. Lets say this month is september and has 30 days like in example, but next month there will be 31 days.. so he needs to add another column that needs to be calculated also

2. Lets say business is going well.. and we need another worker.. When a new row is added, what to do to make it also calculated?

3. Lets say some new "letter" is introduced to schedule, i can easily say that add it to the Legend sheet, but how can i make excel instantly count that new letter to be in calculations?

You're welcome.

1) Make the table on each monthly sheet have 31 columns (even on months with fewer days), but just leave the extra dates blank.

2 & 3) Convert the ranges to actual Tables (highlight the range, say A3:AH6) and on the Insert Tab, click Table
Then your ranges in formulas will auto adjust as data is added to the table.

3d question addon before closing this topic :D

atm letters are sorted ascending, but when he adds a new letter just by adding it down by the other letters and it happens to be not in ascending way.. can i make it automatically be ascending?