hi, JM !

Here's the deal: I know SUMPRODUCT is capable of comparing multiple values against an array by using {}'s like this:
Cell A1: SUMPRODUCT((ARRAY={Option1,Option2,Option3})*ARRAY 2SUM)
BUT can the criteria range be made to reference a cell value??? Something like this:
Cell A1: SUMPRODUCT((ARRAY={A2})*ARRAY2SUM)
Cell A2: "Option1,Option2,Option3"
This would be soooo helpful if possible.

if you need to handle a 'constant-array' through a cell-reference-style in order to maintain flexibility [I guess]...
you could use/define a named-formula using the old xl4 macro-function: Evaluate(text) [i.e.]

op1: assuming 'A2' with - Option1,Option2,Option3 [NO double quotes]
1) select the appropriate cell where you will use the array reference from 'A2' and go to...
2) [menu] insert / name / define... i.e. myArray
3) use this formula: - =evaluate("{"""&substitute(!a2,",",""",""")&"""}") &rept("",0*now())
adjust for any [semi]absolute/relative cell reference... !$a$2 ... !$a2 ...!a$2

op2: assuming 'A2' with - {"Opton1","Option2","Option3"} [braces and double quotes included]
1) idem
2) idem
3) use this formula: - =evaluate(!a1)&rept("",0*now())

be aware that using xl4 macrofunctions with 'this' cell-reference-style could result in application's 'crash'
- if you copy/paste cells using 'the name' across sheets/books [and depending on excel version] :-(

hth,
hector.