Thread: Array Help

Having a brain Fuzz…….
What am I missing…..The following array should return “5� if cell A1
contains “ABC� and cell B1 contains “DEF� and cell C1 contains “5�. It is
returning “0�. Any help would be greatly appreciated. Thanks.

{=SUM(IF(AND($A$1:$A$20="ABC", $B$1:$B$20="DEF"),$C$1:$C$20,0))}

try this

=SUMPRODUCT(($A$1:$A$20="ABC")*(B$1:$B$20="DEF"),$C$1:$C$20)


"Airfive" <Airfive@discussions.microsoft.com> wrote in message
news:009E0A67-CC2C-4168-AD5E-39D780EE7421@microsoft.com...
> Having a brain Fuzz...
> What am I missing...The following array should return "5" if cell A1
> contains "ABC" and cell B1 contains "DEF" and cell C1 contains "5". It is
> returning "0". Any help would be greatly appreciated. Thanks.
>
> {=SUM(IF(AND($A$1:$A$20="ABC", $B$1:$B$20="DEF"),$C$1:$C$20,0))}
>

use this instead
{=SUM(IF(AND($A$1:$A$20="ABC",$B$1:$B$20="DEF"),$C$1:$C$20))}

"Airfive" <Airfive@discussions.microsoft.com> wrote in message
news:009E0A67-CC2C-4168-AD5E-39D780EE7421@microsoft.com...
> Having a brain Fuzz...
> What am I missing...The following array should return "5" if cell A1
> contains "ABC" and cell B1 contains "DEF" and cell C1 contains "5". It is
> returning "0". Any help would be greatly appreciated. Thanks.
>
> {=SUM(IF(AND($A$1:$A$20="ABC", $B$1:$B$20="DEF"),$C$1:$C$20,0))}
>

You can't have AND in an array function like this, easiest would be

=SUMPRODUCT(--(A1:A20="ABC"),--(B1:B20="DEF"),C1:C20)

entered normally, if yoiu insist in using IF and entering it with ctrl +
shift & enter
use

=SUM(IF(($A$1:$A$20="ABC")*($B$1:$B$20="DEF"),$C$1:$C$20,0))


Regards,

Peo Sjoblom

"Airfive" wrote:

> Having a brain Fuzz…….
> What am I missing…..The following array should return “5� if cell A1
> contains “ABC� and cell B1 contains “DEF� and cell C1 contains “5�. It is
> returning “0�. Any help would be greatly appreciated. Thanks.
>
> {=SUM(IF(AND($A$1:$A$20="ABC", $B$1:$B$20="DEF"),$C$1:$C$20,0))}
>

Airfive wrote...
>What am I missing.....The following array should return "5" if
cell A1
>contains "ABC" and cell B1 contains "DEF" and cell C1 contains
"5". It is
>returning "0". Any help would be greatly appreciated. Thanks.
>
>{=SUM(IF(AND($A$1:$A$20="ABC", $B$1:$B$20="DEF"),$C$1:$C$20,0))}

AND (and OR for that matter) don't provide pairwise boolean operations.
They *only* return *single* values.

The reason the formula above returns 0 is no doubt due to A1:A20 not
*ALL* being equal to "ABC" and B1:B20 not *ALL* being equal to "DEF".
That is, if A1:B4 contained
{"ABC","DEF";"ABC","bar";"foo","DEF";"foo","bar"},

AND(A1:A4="ABC",B1:B4="DEF")

would evaluate as

== AND({"ABC";"ABC";"foo";"foo"}="ABC",{"DEF";"bar";"DEF";"bar"}="DEF")
== AND({TRUE;TRUE;FALSE;FALSE},{TRUE;FALSE;TRUE;FALSE})
== AND(TRUE,TRUE,FALSE,FALSE,TRUE,FALSE,TRUE,FALSE)
== FALSE

What you want is
=SUMPRODUCT(--($A$1:$A$20="ABC"),--($B$1:$B$20="DEF"),$C$1:$C$20)

Thank you all for your replies and help. This forum is a goldmine of
information.

"hrlngrv@aol.com" wrote:

> Airfive wrote...
> >What am I missing.....The following array should return "5" if
> cell A1
> >contains "ABC" and cell B1 contains "DEF" and cell C1 contains
> "5". It is
> >returning "0". Any help would be greatly appreciated. Thanks.
> >
> >{=SUM(IF(AND($A$1:$A$20="ABC", $B$1:$B$20="DEF"),$C$1:$C$20,0))}
>
> AND (and OR for that matter) don't provide pairwise boolean operations.
> They *only* return *single* values.
>
> The reason the formula above returns 0 is no doubt due to A1:A20 not
> *ALL* being equal to "ABC" and B1:B20 not *ALL* being equal to "DEF".
> That is, if A1:B4 contained
> {"ABC","DEF";"ABC","bar";"foo","DEF";"foo","bar"},
>
> AND(A1:A4="ABC",B1:B4="DEF")
>
> would evaluate as
>
> == AND({"ABC";"ABC";"foo";"foo"}="ABC",{"DEF";"bar";"DEF";"bar"}="DEF")
> == AND({TRUE;TRUE;FALSE;FALSE},{TRUE;FALSE;TRUE;FALSE})
> == AND(TRUE,TRUE,FALSE,FALSE,TRUE,FALSE,TRUE,FALSE)
> == FALSE
>
> What you want is
> =SUMPRODUCT(--($A$1:$A$20="ABC"),--($B$1:$B$20="DEF"),$C$1:$C$20)
>
>