How to sum up the values of the working schedule symbols from a range that matching the value legend for these characters

[Roumen Roussev]

I have a shift schedule with symbol and legend with duration value of each shift symbol. I want to summarize the value corresponding to the symbols for each employee.

Now I use:
=COUNTIF($D5:$AH5;$A$13)*($N$13*24)+COUNTIF(D5:AH5;$A$14)*($N$14*24)+COUNTIF(D5:AH5;$A$15)*($N$15*24)
where
D5:AH5 is range with shift symbols
A13:A15 is range for symbols fom legend
N13:N15 is range of corresponding symbols value with "hh:mm" formatting
Image: https://ibb.co/WpQfQyY

This is the WeTransfer link to an Excel file. https://we.tl/t-eyGTJAH9KB

 

[Fluff]

Hi & welcome to MrExcel.
You could use
=SUMPRODUCT((D5:AH5=A13:A34)*(N13:N34))*24
but for it to work you will need to remove the merged cells A17:A18
you will also need a value of 0 in col N rather than ""

 

[Roumen Roussev]

Hi & welcome to MrExcel.
You could use
=SUMPRODUCT((D5:AH5=A13:A34)*(N13:N34))*24
but for it to work you will need to remove the merged cells A17:A18
you will also need a value of 0 in col N rather than ""

Thank you very much. The solution works perfectly without merged cells .

 

[Fluff]

You're welcome & thanks for the feedback

 

[Roumen Roussev]

I do not know whether it's right to continue the topic, but I'm faced with another problem in the formula.
I need to use the formula so as to summarize only if another condition for comparison is fulfilled in the second range. I have identical symbols that differ only by criteria in another column
I tried:
=SUMPRODUCT((AND((D5:AH5=$A$35:$A$63);(A5=$C$35:$C$63))*($N$35:$N$63))*24)
But it doesn't work.
This is WeTransfer link to an Excel file. https://we.tl/t-acRK0YoCGO

 

[Fluff]

Try
=SUMPRODUCT(((D5:AH5=$A$35:$A$63)*(A5=$C$35:$C$63)*($N$35:$N$63))*24)

 

[Roumen Roussev]

You are excel genius. Thanks a lot.

 

[Fluff]

You're welcome & thanks for the feedback