Array Constant

[K]

A B C…….col
AAA 20 AAA
SSS 30 DDD
DDD 40 NNN
VVV 50
NNN 60

Formula 1: SUMPRODUCT(($A$1:$A$5={"AAA","DDD","NNN"})*($B$1:$B$5))

Formula 2: SUMPRODUCT(($A$1:$A$5={C1,C2,C3})*($B$1:$B$5))


Hi all, I got data in columns A,B and C (as shown above). I used
Formula 1 (as shown above) in cell D1 which worked fine and came up
with result 120. But when I used Formula 2 in cell D1 (as shown
above) in which I put cell references in array constant instead of
text, i received error message. Is it possible that i can use cell
references in constant array? Please can any friend can help me on
this.

 

[James Ravenswood]

  A        B         C…….col
AAA     20      AAA
SSS     30      DDD
DDD     40      NNN
VVV     50
NNN     60

Formula 1: SUMPRODUCT(($A$1:$A$5={"AAA","DDD","NNN"})*($B$1:$B$5))

Formula 2: SUMPRODUCT(($A$1:$A$5={C1,C2,C3})*($B$1:$B$5))

Hi all,  I got data in columns A,B and C (as shown above).  I used
Formula 1 (as shown above) in cell D1 which worked fine and came up
with result 120.  But when I used Formula 2 in cell D1 (as shown
above) in which I put cell references in array constant instead of
text, i received error message.  Is it possible that i can use cell
references in constant array?  Please can any friend can help me on
this.

That is because an array is an array of constants. If you enter:
={"James","David"}
in a cell, no error results
if you enter:
={A1,A2}
in a cell, an error results.

 

[Don Guillett]

That is because an array is an array of constants.  If you enter:
={"James","David"}
in a cell, no error results
if you enter:
={A1,A2}
in a cell, an error results.- Hide quoted text -

- Show quoted text -

So, instead you could use EDIT>REPLACE to change your formulas.....

 

[joeu2004]

Is it possible that i can use cell references in
constant array?

Not literally. By definition, an array constant is an array of
__constants__.

Formula 2: SUMPRODUCT(($A$1:$A$5={C1,C2,C3})*($B$1:$B$5))

You can express that as:

=SUMPRODUCT(--ISNUMBER(MATCH($A$1:$A$5,C1:C3,0)),$B$1:$B$5)

The double-negative (--) converts TRUE and FALSE to 1 and 0, just your
use of multiply (*) does.