Conditional Sum Problem

[Andrew Mackenzie]

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")*(Report="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.

 

[Bob Phillips]

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]

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]

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]

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