Two identical formulas for a different result?

H

hochedez

Hello everyone, :)

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

I don't understand why my first formula works, and the second and thir
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 an
IncomeFeeId is RB...

What am I missing?

Thank you so much for your help
 
B

bj

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
 
E

Eric

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

Eric
 
H

hochedez

Thanx for replying.

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

To be a little more precise, I use the defined ranges in a tabl
created by a SQL query (so the number of cells change in the define
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 th
mistake here...
And I really want to know what I'm doing wrong!

:confused
 
B

bj

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
 
H

hochedez

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
 
Top