=SUMPRODUCT(($A1:$A64000=E$2)*($C1:$C64000=$E4)*( $D1:$D64000))
One final review on the sum-range...
ALL cells in that range need to have a number!

Not if you use this syntax:

=SUMPRODUCT(--($A1:$A64000=E$2),--($C1:$C64000=$E4),$D1:$D64000)

http://xldynamic.com/source/xld.SUMPRODUCT.html

--
Biff
Microsoft Excel MVP


<Emiel Wielinga wrote in message
...
I had the same problem.
SUMPRODUCT works fine in excel 2007.
But in excel 2003 it returned: #NUM!

=SUMPRODUCT(--($A:$A=E$2);--($C:$C=$E4);--($D:$D))
same for:
{=SUM(IF(($A:$A=E$2)*($C:$C=$E4);$D:$D))}
And obviously,
=SUMIFS(...)
doesn't work at all in pre-2007.

With the previous info in this thread it became clear you can not use
entire columns!

Obvious solution:
=SUMPRODUCT(($A1:$A64000=E$2)*($C1:$C64000=$E4)*($ D1:$D64000))
But that returned: #VALUE!

One final review on the sum-range...
ALL cells in that range need to have a number!
I filled up the empty cells with "0" and the formula finally gave me the
result "6".
This is the correct result for me :-)

NOTE:
As previously mentioned, you can also use the {=SUM(IF(...)} formula.
Which is not troubled by empty cells. Just don't use entire columns in a
range ;-)



e.g. type in cell:
=SUM(IF((A1:A64000=E1)*(C1:C64000=F1);D1:D64000))
(use <ctrl+<shift+<enter to properly save formula)



T. Valko wrote:

I do not have Excel 2003 but in Excel 2002 this returns #NUM!
26-Feb-10

I do not have Excel 2003 but in Excel 2002 this returns #NUM!:

=SUMPRODUCT(--(A:A=""))

--
Biff
Microsoft Excel MVP


"Joe User" <joeu2004 wrote in message

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Creating a WPF Custom Control
http://www.eggheadcafe.com/tutorials...ustom-con.aspx