If it behaves the same way with ranges rather than names I am flummoxed. As
I say, it doesn't happen here, with 1,2 or 3 ranges. I cannot remember how
to get #N/A with SUMPRODUCT, I can only create conditions that generate a
#VALUE. Are you testing the ranges correctly, you know Balance - A2:A& etc.?

Do you want to send me the workbook to see? Put Excel - Misc - Andrew
Mackenzie in the subject,

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Andrew Mackenzie" wrote in message
...
It works for me if I have just one condition. If I leave out the
Report="Y"
condition its fine. So I agree that there appears to be something wrong
with the way the named range is set up BUT it also doesn't work if I
replace
the name with the range. I also tried swapping Y and N for 1 and 0 but
this
didn't work either. Is there anything else I should look at in terms of
the
way the range is named?

Cheers,

Andrew.

"Bob Phillips" wrote in message
...
Andrew,

It works fine for me with that data. Are you sure the names are
correctly
setup. Try it with actual ranges rather than names.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Andrew Mackenzie" wrote in message
...
I have three columns of data something like this:

A B C
1 Balance Report Product Type
2 3,000 Y Oranges
3 1,000 N Oranges
4 4,000 N Apples
5 500 Y Pears
6 2,500 N Pears
7 8,000 Y Oranges

Range A2:A7 is named "Balance", B2:B7 is named "Report" and C2:C7 is
named
"Product_Type".

I am trying the following:

=SUMPRODUCT((Balance)*(Product_Type="Oranges")*(Re port="Y"))

The result I am getting is #N/A and I can't figure out what I am doing
wrong.

Can anyone help me?

Thanks,

Andrew.