Two identical formulas for a different result?

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",Amount,0),0))}

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

=SUMPRODUCT((FiscYear="2003")*(IncomeFeeID="RB"),Amount)
(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!

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",Amount,0),0))}
>
> =SUMPRODUCT((FiscYear="2003")*(IncomeFeeID="RB")*Amount)
> (result is #value)
>
> =SUMPRODUCT((FiscYear="2003")*(IncomeFeeID="RB"),Amount)
> (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",Amount,0),0))}
> >
> > =SUMPRODUCT((FiscYear="2003")*(IncomeFeeID="RB")*Amount)
> > (result is #value)
> >
> > =SUMPRODUCT((FiscYear="2003")*(IncomeFeeID="RB"),Amount)
> > (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!

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