To my knowledge, SUMPRODUCT does not return #NUM.
At least, that is the case for Excel 2003

I don't have Excel 2003 but in Excel 2002 this returns #NUM!:

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

--
Biff
Microsoft Excel MVP


"Joe User" <joeu2004 wrote in message
...
"Ann Scharpf" wrote:
=SUMPRODUCT(--(YEAR(PayDate)=YEAR(TODAY())),LWOP)
[....]
Now I am getting a #NUM! errror. I looked in
help and it said this happens when you have
non-numeric data.

Let's clear up some facts. Are you getting a #NUM or a #VALUE error?

To my knowledge, SUMPRODUCT does not return #NUM. At least, that is the
case for Excel 2003. Moreover, Excel 2003 Help describes #NUM as an
invalid
numeric value, not when you have non-numeric data when numeric data is
expected. Compare SQRT(-1) and SQRT("oops"). On the other hand, Excel
2003
does return a #VALUE in that case. Consider --"oops".


I just have numbers, dates and the column headings.

Whereas SUMPRODUCT will tolerate non-numeric data (e.g. column headings)
in
LWOP, YEAR does not tolerate non-numeric data. Yet SUMPRODUCT requires
that
the size of ranges, PayDate and LWOP, be the same.

Therefore, those ranges cannot include the column headings.

However, again, YEAR(PayDate) would return a #VALUE error, not #NUM, if it
encounteres non-numeric data.

If you are truly getting a #NUM error, either one of the cells referenced
by
PayDate or LWOP contains a #NUM error, or your version of Excel (which?)
behaves differently than Excel 2003.

If you cannot get help constructive assistance in this forum -- it is very
difficult to debug worksheets at arm's length -- feel free to send me the
Excel file. Send it to joeu2004 "at" hotmail.com.


----- original message -----

"Ann Scharpf" wrote:

Bob Phillips & Glen helped me out with a SUMIF() question I posted
earlier
today. Both of you recommended that I use this formula:

=SUMPRODUCT(--(YEAR(PayDate)=YEAR(TODAY())),LWOP)

(I changed the name of the range from Date to PayDate so I don't use a
reserved function name.)

Now I am getting a #NUM! errror. I looked in help and it said this
happens
when you have non-numeric data. I've gone through the entire columns in
question (PayDate and LWOP) and deleted the values from all cells that
should
be blank. I just have numbers, dates and the column headings. I'm sorry
to
be a pain here but can you tell me what else I've done wrong?

Thanks again!


--
Ann Scharpf