"Strange" array formula issue
hello -
Thanks to these Groups, I've become quite proficient at using array
formulas to evaluate fairly complex calculations. However, I've run
into a small issue that I just cannot understand and it seems like it
shouldn't be so difficult...
This array formula works fine: ("week" is a Named cell)
{=SUM(IF(ISERROR($M$3:$M$11000),0,$M$3:$M$11000)*( $G$3:$G$11000=($A9&week)))}
This array formula DOES NOT work fine:
{=SUM(IF(ISERROR($M$3:$M$11000),0,$M$3:$M$11000)*( $J$3:$J$11000="C")*($G$3:$G$11000=($A9&week)))}
The difference btw the two formulas is this: ($J$3:$J$11000="C")
The strange part that this formula DOES work:
=COUNTIF($J$3:$J$11000,"C")
Is there some fundamental difference in evaluating a range using an
array formula vs the COUNTIF function?
I've tried evaluating the 'bad' part of the array formula to a VALUE
and to TEXT, but no change...
Any ideas?
TIA,
Ray
|