#NUM! Error

[Dick]

Getting #NUM! error with this formula
=SUMPRODUCT((Sheet1!A:A="Alkalume 143")*(Sheet1!B:B="January"),Sheet1!
D)
Can someone help as to why.
Using excel 2003, saved as xls
Thanks in advance

 

[joeu2004]

Getting #NUM! error with this formula
=SUMPRODUCT((Sheet1!A:A="Alkalume 143")
*(Sheet1!B:B="January"),Sheet1!D)
Can someone help as to why.
Using excel 2003, saved as xls

XL2003 does not support references in the form A:A in SUMPRODUCT.
That is a feature of XL2007 and later.

In XL2003, you need to write A1:A1000 or whatever is appropriate.

 

[Dave Peterson]

You can't use the entire column in xl2003. (xl2007+ will allow them.)

If you have any non-numeric (non-error) data in column D, you may want to use
this syntax:

=SUMPRODUCT(--(Sheet1!A1:A111="Alkalume 143"),
--(Sheet1!B1:B111="January"),
Sheet1!D1111)

(adjust the rows to match your data)

Just like =sum(d1:d111) will ignore non-numeric text values, so will
=sumproduct() when you use commas (not multiply).