|
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" 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) |
| All times are GMT +1. The time now is 01:48 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com