As I said:

If you mean a real blank, (ie and empty cell), not an empty string, (ie
"")

Where are the "" and "NA" coming from?

there must be some way of getting it to return a text value?

SUMPRODUCT() as the *product* part suggests, works with numbers, not text.
It can compare text but not return text.

Do you want to send me a sample sheet so that we both know what it is that
you are trying to do? If so then replace the part after the @ in my address
below as it says.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"mass" wrote in message
...
the problem is that it is reading the NA or a blank in the special pricing
column and returning a zero. since the IF function relates to "" or "NA",
it
is not catching them.
Unfortunately, "" or "NA" is not the same as 0 which is a special price in
certain cases.
there must be some way of getting it to return a text value?

"Sandy Mann" wrote:

I can't seem to apply it with an IF() formula for the special pricing
if
the
lookup pulls a blank

If you mean a real blank, (ie and empty cell), not an empty string, (ie
"")
then

=IF(SUMPRODUCT(<Your data for special rate)=0,SUMPRODUCT(<Your data for
standard rate),SUMPRODUCT(<Your data for special rate))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"mass" wrote in message
...
for a bunch of calling destinations, i have two price lists. The first
price
list is special rates which applies only to half the destinations. The
second price list is standard that applies to all.

Now i have a call list column which is a list of calls with call
destinations that are in two columns (destination, type of call- eg
fixed
or
mobile).

I want to pull the rate for each call in the call list against the
special
price list. But if the special price list is blank (because there is no
special rate- this is not the same as 0) then i want it to revert to
the
standard price list.

Right now, i am using sumproduct to check both the destination country
and
type of call in the call list, against the same in the price list and
it
returns the standard price correctly.

I can't seem to apply it with an IF() formula for the special pricing
if
the
lookup pulls a blank.
Thanks

"Sandy Mann" wrote:

Can SUMPRODUCT() return Text?

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Gary''s Student" wrote in
message
...
=if(or((sumproduct()=""),(sumproduct()="text")),an othervalue,sumproduct())

--
Gary''s Student - gsnu200759