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

Hi,

I am using a formule as: =SUM(SUMIFS($E:$E;$A:$A;H$1;$C:$C;{"*appel*";"*ban aan*"}))

However the values {"*appel*";"*banaan*"} change once in a while and i do not want to change the formula everytime (to add or remove a product.

Is it possible to replace the {"*appel*";"*banaan*"} by a reference cell or reference cells? something like:
=SUM(SUMIFS($E:$E;$A:$A;H$1;$C:$C;{X1}))

i tried various things and looked on the forum but i am not able to find an answer on how to do this. I attached an example of the sheet to this thread, any help or ideas are welcome.

Thanks,
Wilco

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)

Hi,

Thanks a lot for this, I never heard of Named Ranges and that you can use these in formulas. This is really great!

How did you learn all these things? can you recommend any good Excel learning books/websites/etc...

Thansk again!

Wilco