How To Use Or Pass A Dynamic Reference In An Array Formula

[Excel Fox]

Use

=SUMPRODUCT(SUMIFS($D:$D,$A:$A,H$1,$C:$C,Prod1))

Or

=SUMPRODUCT(SUMIFS($D:$D,$A:$A,H$1,$C:$C,"*"&Prod2 &"*")) and ensure that the named range is non-empty, and has only values that you need, and for the others use something that you are sure will never be in the list... like "XXX"

I've shown two methods in the attached workbook that can replicate the 'contains' feature. One uses the wild character "*" on either sides of the values in the cell, and the other uses this as a concatenation to the named range (second formula above)