Array Constant

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

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

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top