"Strange" array formula issue

[Ray]

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

 

[Bob Phillips]

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]

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") ....

 

[Bob Phillips]

Maybe so, but whereas SUMIF ignores those errors, SUMPRODUCT does not. By
bad data, I mean a cell with #VALUE or #REF within it.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Ray]

I'm not using SUMPRODUCT, just SUM as an array formula -- OR, does that
make them the same?

And even if there are 'errors' (ie #VALUE or #REF), doesn't the first
part of my formula ( ie IF(ISERROR... ) take care of those errors?
I've used this logic before on other arrays containing 'bad data' and
it seemed to work fine.

I just can't figure out how this situation is different...

 

[Bob Phillips]

Alright, SUM(IF then <g>, it is still not SUMIF. They have been coded by
different teams, and so maybe the quality control wasn't quite there.

And the error trapping won't catch it if it tests column M and the error is
in J.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)