SUMPRODUCT Error

[Madiya]

I am using named ranges from the other workbook but I am getting #N/A
error.
My formula is as below.
=SUMPRODUCT((ccc=A6),(rrr<>""))
=SUMPRODUCT(-(ccc=A6),-(rrr<>""))

When I use counta with ccc or rrr, I am getting correct answer.
But now I want the count of non blank rrr where ccc value equals value
of A6.

Please help me to sort out this error.

Regards,
Madiya

 

[Guest]

Excel can only resolve names from the same workbook, unless you tell it where
to look. Thus you could use something like
=SUMPRODUCT(-(Book4!ccc=A6),-(Book4!rrr<>""))

Your first formula would not even work from the same workbook, because
(ccc=A6) and (rrr<>"") are arrays of booleans, not numbers, and therefore are
skipped by SUMPRODUCT. In your second formula, the minus signs coerce the
arrays to 0's and 1's.

Note that if you add a third condition to your second fomrula, you would get
a negative sum, therefore it is more common to use -- instead of - to coerce.

Jerry

 

[Madiya]

Excel can only resolve names from the same workbook, unless you tell it where
to look. Thus you could use something like
=SUMPRODUCT(-(Book4!ccc=A6),-(Book4!rrr<>""))

Your first formula would not even work from the same workbook, because
(ccc=A6) and (rrr<>"") are arrays of booleans, not numbers, and therefore are
skipped by SUMPRODUCT. In your second formula, the minus signs coerce the
arrays to 0's and 1's.

Note that if you add a third condition to your second fomrula, you would get
a negative sum, therefore it is more common to use -- instead of - to coerce.

Jerry








- Show quoted text -

Jerry,
Thanks for your help,
but need some more help.
My name range ccc contains full referancce to the other workbook
range.
The refers to box contains this...
='C:\Documents and Settings\reliance\Desktop\[RUIM new.xls]Damage
Receipt'!$C$1:$C$20000
If this is not enough then I need to checg the formulas but since it
is full referance it should work.
Can you pl help me understanding this........ hope I am not asking too
much.

Regards,
Madiya