On Aug 2, 5:45 pm, "Bob Phillips" wrote:
I would bet that the range ccc and rrr are different sizes. SP expects them
to be the same size, else it throws an #NO/A.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Madiya" wrote in message

ups.com...



On Aug 2, 3:01 pm, "Peter T" <peter_t@discussions wrote:
Hi Madiya,

Your formula works fine for me (the "-" signs are not necessary). If the
named ranges are in another workbook they need to be prefixed with
Bookname.xls!, possibly bracketed with apostrophes.

In the input bar, select ccc=A6 and rrr<"". You should see a series of
{True, False, ...}. Press Esc to reset the formula.

Regards,
Peter T

"Madiya" wrote in message

groups.com...

I have created named range in from other workbook.
When I use it in Counta(ccc) it gives correct answer.

However if I use in the sumproduct formula, I get #NA error.
The formula is =SUMPRODUCT(-(ccc=A6)*-(rrr<""))
(I want count of non blank cells in rrr range where range ccc has a
value equall to A6)

I suspect that something is wrong with formula.

Pl help urgently.

Regards,
Madiya- Hide quoted text -

- Show quoted text -

Thanks for reply.
But I am still getting error in the sumproduct formula.
I have tried all type of variations but it doesn't work.
Any thoughts?
Do I have to enable some options or something like that?

Regards,
Madiya- Hide quoted text -

- Show quoted text -

Hi Bob,
Nice to meet you again.
I have checked and rechecked the same. Both the range is of same size.
I have also created small range of 20 cells for testing purpose, the
that also gives error.
Apart from that when I use them individually, it gives correct count.

Additional details which may be of some help:
The range in referance is a dump from SAP.
ccc refers to party code numbers which is stored in general format by
excel.
I tried coverting to number format by F2 and Enter but of no help.
rrr refers to a 16 digit alpha numaric code.
Both the range may contain blanks.

Regards,
Madiya