SUMPRODUCT has never let me down BUT...

J

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})*ARRAY2SUM)

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.

Thanks in advance.

-- John
 
J

Jerry W. Lewis

You cannot use {} syntax with a cell reference. However, if Array and
Array2SUM are appropriately sized, then
SUMPRODUCT((ARRAY=A1)*ARRAY2SUM)
will work.

Jerry
 
B

Biff

Array constants can't be references.

List your criteria in cells:

F1 = option1
F2 = option2
F3 = option3

=SUMPRODUCT(--(ISNUMBER(MATCH(Array1,F1:F3,0))),Array2)

Biff
 
H

Héctor Miguel

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})*ARRAY2SUM)
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.
Click to expand...

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.
 
B

Bob Phillips

Isn't that just

=SUMIF(ARRAY,A1,ARRAY2SUM)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Top