Hello everyone, :)

A very quick question, but nonetheless very intriguing to me (I just
lost 4h on that :( )

I don't understand why my first formula works, and the second and third
don't.

{=SUM(IF(FiscYear="2003",IF(IncomeFeeID="RB",Amoun t,0),0))}

=SUMPRODUCT((FiscYear="2003")*(IncomeFeeID="RB")*A mount)
(result is #value)

=SUMPRODUCT((FiscYear="2003")*(IncomeFeeID="RB"),A mount)
(this one could work too I thought, but result in a 0)

I am simply trying to sum (amount) when the fiscal year is 2003 and
IncomeFeeId is RB...

What am I missing?

Thank you so much for your help!


--
hochedez
------------------------------------------------------------------------
hochedez's Profile: http://www.excelforum.com/member.php...fo&userid=5734
View this thread: http://www.excelforum.com/showthread...hreadid=465897

what are the addresses for your defined ranges?
do you have any merged cells?
I am most confused by the difference in response for the two sumproduct
formulas

"hochedez" wrote:


Hello everyone, :)

A very quick question, but nonetheless very intriguing to me (I just
lost 4h on that :( )

I don't understand why my first formula works, and the second and third
don't.

{=SUM(IF(FiscYear="2003",IF(IncomeFeeID="RB",Amoun t,0),0))}

=SUMPRODUCT((FiscYear="2003")*(IncomeFeeID="RB")*A mount)
(result is #value)

=SUMPRODUCT((FiscYear="2003")*(IncomeFeeID="RB"),A mount)
(this one could work too I thought, but result in a 0)

I am simply trying to sum (amount) when the fiscal year is 2003 and
IncomeFeeId is RB...

What am I missing?

Thank you so much for your help!


--
hochedez
------------------------------------------------------------------------
hochedez's Profile: http://www.excelforum.com/member.php...fo&userid=5734
View this thread: http://www.excelforum.com/showthread...hreadid=465897

I think BJ got it right - check to see if the ranges have the same number of
cells.

Eric

"bj" wrote:

what are the addresses for your defined ranges?
do you have any merged cells?
I am most confused by the difference in response for the two sumproduct
formulas

"hochedez" wrote:


Hello everyone, :)

A very quick question, but nonetheless very intriguing to me (I just
lost 4h on that :( )

I don't understand why my first formula works, and the second and third
don't.

{=SUM(IF(FiscYear="2003",IF(IncomeFeeID="RB",Amoun t,0),0))}

=SUMPRODUCT((FiscYear="2003")*(IncomeFeeID="RB")*A mount)
(result is #value)

=SUMPRODUCT((FiscYear="2003")*(IncomeFeeID="RB"),A mount)
(this one could work too I thought, but result in a 0)

I am simply trying to sum (amount) when the fiscal year is 2003 and
IncomeFeeId is RB...

What am I missing?

Thank you so much for your help!


--
hochedez
------------------------------------------------------------------------
hochedez's Profile: http://www.excelforum.com/member.php...fo&userid=5734
View this thread: http://www.excelforum.com/showthread...hreadid=465897

Thanx for replying.

Yes, indeed the defined ranges do have the number of cells. I too
thought the problem might come from there but replacing the "defined
ranges" with the cell rangesm (ie. A2:A30 C2:C30 and F2:F30) didn't
change anything.

To be a little more precise, I use the defined ranges in a table
created by a SQL query (so the number of cells change in the defined
ranges change, when i update it, but they keep a similar lenght).

I did it before, and I know it worked, but I just can't find the
mistake here...
And I really want to know what I'm doing wrong!




--
hochedez
------------------------------------------------------------------------
hochedez's Profile: http://www.excelforum.com/member.php...fo&userid=5734
View this thread: http://www.excelforum.com/showthread...hreadid=465897

try opening up the first of your sumproduct equations and hilighting each of
the named ranges in turn and pressing F9,
verify that it is giving you a similar sized array for each range
The #value indicates a non equal array
the 0 in the second one indicates either thay are out of order or it is
treating the values as 0 or text

"hochedez" wrote:


Thanx for replying.

Yes, indeed the defined ranges do have the number of cells. I too
thought the problem might come from there but replacing the "defined
ranges" with the cell rangesm (ie. A2:A30 C2:C30 and F2:F30) didn't
change anything.

To be a little more precise, I use the defined ranges in a table
created by a SQL query (so the number of cells change in the defined
ranges change, when i update it, but they keep a similar lenght).

I did it before, and I know it worked, but I just can't find the
mistake here...
And I really want to know what I'm doing wrong!




--
hochedez
------------------------------------------------------------------------
hochedez's Profile: http://www.excelforum.com/member.php...fo&userid=5734
View this thread: http://www.excelforum.com/showthread...hreadid=465897

Thank you bj, it helps.

The error was quite simple in the end (sorry...)
The defined ranges included the column name, problem solves now.

:)

Ben


--
hochedez
------------------------------------------------------------------------
hochedez's Profile: http://www.excelforum.com/member.php...fo&userid=5734
View this thread: http://www.excelforum.com/showthread...hreadid=465897