#NUM! Error

  • Thread starter Thread starter Dick
  • Start date Start date
D

Dick

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

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.
 
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!D1:D111)

(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).
 
Back
Top