AND in array formula

damezumari · Feb 10, 2006

{=SUM(E13:AE13*IF(TEXT(E$7:AE$7,"mmmm")=mes,1,0))} gives 15.05767168

{=SUM(E13:AE13*IF(AND(TEXT(E$7:AE$7,"mmmm")=mes,TRUE),1,0))} gives 0

mes is the range name for a cell that contains the name of a month.

The top array formula works as expected, but why does the bottom one
give 0. It should give the same result as the top one as I have only
added TRUE in an AND condition.

Bernard Liengme · Feb 10, 2006

You cannot use Boolean ops in array formulas; you must use operators (*)
and (+) for AND and OR respectively.

Have you thought of using SUMPRODUCT?
=SUMPRODUCT(--(TEXT(E$7:AE$7,"mmmm")=mes),E13:AE13)
See
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

and

http://mcgimpsey.com/excel/formulae/doubleneg.html

best wishes

AND in array formula

damezumari

Bernard Liengme