Array Help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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...
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))}
Click to expand...

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)
 
Back
Top