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" wrote:

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