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.

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.

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.

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.

Hi Bob,

Thanks for your efforts (and in fact your help becuase you pointed me in the
right direction). I have now figured out the problem - my named ranges did
not all start and end with the same row.

Andrew


"Bob Phillips" wrote in message
...
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.