Named Range in Other workbook - not working in SUMPRODUCT formula?

[Madiya]

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

 

[Peter T]

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]

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










- 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

 

[Bob Phillips]

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]

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)








- 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