ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Conditional Sum Problem (https://www.excelbanter.com/excel-discussion-misc-queries/60541-conditional-sum-problem.html)

Andrew Mackenzie

Conditional Sum Problem
 
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.




Bob Phillips

Conditional Sum Problem
 
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.






Andrew Mackenzie

Conditional Sum Problem
 
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.








Bob Phillips

Conditional Sum Problem
 
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.










Andrew Mackenzie

Conditional Sum Problem
 
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.













All times are GMT +1. The time now is 04:14 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com