The recommendation is not to have Merged Cells, and instead list the date in every cell in column A, then you can use:


=SUMPRODUCT(--(A3:A6=I2),--(B3:B6=J2),D3:D6)

where I2 and J2 contain the Date and Shift to look up, respectively.

If you must have the date column that way, then add a helper column, e.g. in E3: =LOOKUP(9.999E+307,A$3:A3) copied down and then use

=SUMPRODUCT(--(E3:E6=I2),--(B3:B6=J2),D3:D6) with same conditions above.