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" wrote in message
...
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" wrote in message
...
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.

" 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)