[Solved] Formula to sum values for codes in a row

[RaezoR]

I have a working spreadsheet that is for a Turnus / Shift work.

The Calendar has, week number, Mondays date in that week, then shift codes under the days, horizonatally in the first 2 rows, and so on.

Code: Select all

Week # -   Date -      Monday - Tuesday - Wednesday - Thursday - Friday - Saturday - Sunday 
2          03.01.22    DSV      DSV       DSV         DSV        F1       D1SV       AFDV  

I work shifts that have different codes like DSV or D1SV, these codes are listed at the bottom of the sheet in a row (A56), with a value (work hours) in next cell in row (B56).

I need to add up the work hours in each week and divide them by 7.

The challenge is that the formula has to find different codes through the week and add all the hours together and divide to give the answer I am looking for.

I would appreciate some help on this, as searches didn't really help.

Please find file attached.

[FJCC]

Please upload a small sample file showing your layout. To upload a file, click PostReply and look for the Upload Attachment tab just below the box where you type a response.
Does every day have a code, so there are no blank cells?

[MrProgrammer]

Thank you for the attachment. See column J in mine. VLOOKUP gets the values for your codes and SUMPRODUCT adds them.

202106252046.ods

(25.04 KiB) Downloaded 95 times

[Tutorial] VLOOKUP questions and answers
[Tutorial] The SUMPRODUCT function

I work shifts that have different codes like DSV or D1SV, these codes are listed at the bottom of the sheet in a row (A56), with a value (work hours) in next cell in row (B56).

I found your list at A56 to be incomplete. For example it does not have ASV, which is the code in C30:E30 of your attachment. I created my own list in the Codes sheet so you should review the values that I chose for my example.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.

[RaezoR]

Many thanks for your help, I really appreciate it.