Sum up shift schedule symbols values from a range that matching the value legend
 

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

I try to summarize the sum of the symbols from a shift schedule taking the legend values where for each character there is a value of the corresponding row in another column.
Each cell is a dropdown menu that receives information from the legend with the symbols.
That's what I need to be able to dynamically change the symbols and get the right result.
In the second stage I will try to make a button that automatically inserts a new line by keeping the formulas and deleting the symbols.

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

Sum up shift schedule symbols values from a range that matching the value legend
 

Hi Roumen,

Am Mon, 3 Dec 2018 10:03:58 +0000 schrieb Roumen Roussev:

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

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

you must unmerge A17:A18. Then you can use:
=SUMPRODUCT(($A$13:$A$29=D5:AH5)*$N$13:$N$29)*24

Have a look for AJ5:AJ7:
https://1drv.ms/x/s!AqMiGBK2qniTgeAJF9321HfNoZKV1g


Regards
Claus B.
--
Windows10
Office 2016

Thank you so much. The solution works perfectly for the case.