How to use a cell's value (string) as part of a formula in Excel (not within a macro)?

Question

I need to sum a range of values in Excel based on one criterion.

So I want to sum say range B1:B10 based on values of range A1:A10 if the range A1:A10 answers to some criterion e.g. contains the words "apple - juice fresh" or "banana"

So the formula would look like :

=SUMPRODUCT(SUMIF(A1:A10;{"apple - juice fresh";"banana"};B1:B10))

This works as desired. Now, I don't want to have the list hardcoded in the formula. I want the list somewhere in a cell. Say cell C1. And so the cell C1 contains as a value :

{"apple - juice fresh";"banana"}

and I want the formula to be something like :

=SUMPRODUCT(SUMIF(A1:A10;C1;B1:B10))

But this does not work. How can I achieve this ? I tried CONCATENATE(C1) but it does not work.

Herebelow is a sample of what I would like

Answer 1

You can try FILTER() then SUM() with Excel O365

=SUM(FILTER(B2:B8,ISNUMBER(SEARCH(A2:A8,D2))))

If you don't have O365 then try SUMPRODUCT() like

=SUMPRODUCT(--ISNUMBER(SEARCH(A2:A8,D2)),B2:B8)

Edit: If that is the case then go with FILTERXML(). Give a try on

=SUM(FILTER(B2:B8,ISNUMBER(XMATCH(A2:A8,FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2,"{",""),"}",""),CHAR(34),""),", ","</s><s>")&"</s></t>","//s"),0))))