Thanks for the response, Bob ...

What do you mean by 'bad data'? And if it was bad data, why would the
SUMIF formula work ok? It's evaluating against the same critieria
(cell.value="C") ....



On Jan 25, 8:13 am, "Bob Phillips" wrote:
It works for me, I think you must have bad data in column J.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

"Ray" wrote in ooglegroups.com...

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