Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
{=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,TR UE),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. |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "damezumari" wrote in message ups.com... {=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,TR UE),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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Array Formula Not Working with Range with Formulas | Excel Discussion (Misc queries) | |||
Array formula returning wrong results | Excel Discussion (Misc queries) | |||
Suppress array formula #NA | Excel Worksheet Functions | |||
problem with Array Formula | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |