Array Constant

  • Thread starter Thread starter K
  • Start date Start date
K

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.
 
  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.
Click to expand...

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.
 
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 -
Click to expand...

So, instead you could use EDIT>REPLACE to change your formulas.....
 
Is it possible that i can use cell references in
constant array?
Click to expand...

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

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.
 
Back
Top