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.

On Feb 7, 5:46*am, K <kamranr1...@yahoo.co.uk> wrote:
> * 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.

On Feb 7, 9:36*am, James Ravenswood <james.ravensw...@gmail.com>
wrote:
> On Feb 7, 5:46*am, K <kamranr1...@yahoo.co.uk> wrote:
>
>
>
>
>
> > * 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.- Hide quoted text -
>
> - Show quoted text -

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

On Feb 7, 2:46*am, K <kamranr1...@yahoo.co.uk> wrote:
> Is it possible that i can use cell references in
> constant array?

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


On Feb 7, 2:46 am, K <kamranr1...@yahoo.co.uk> wrote:
> 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.