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)