SUMPRODUCT() yielding #NUM!

[Ann Scharpf]

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!

 

[Glenn]

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!

Do "PayDate" and "LWOP" include the column headings?

 

[T. Valko]

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

What version of Excel are you using? Do the named ranges refer to entire
columns? If so, you can't use entire columns as range references with
SUMPRODUCT unless you're using Excel 2007 or later.

 

[Joe User]

=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 -----

 

[T. Valko]

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

=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 -----

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!

 

[Joe User]

I don't have Excel 2003 but in Excel 2002 this returns #NUM!:
=SUMPRODUCT(--(A:A=""))

That example returns #NUM in Excel 2003 as well. It is not documented in
the offline Help page. The problem is that indefinite ranges like A:A are
not permitted with SUMPRODUCT (before Excel 2007?). It must be of the form
A1:A1000.

So returning to Ann's problem, the #NUM error might result if the name
ranges PayDate and LWOP are of the form A:A instead of A1:A1000.

Nevertheless, the PayDate cannot include column headings, if by that Ann
means text. If it does, the YEAR(PayDate) expression will return a #VALUE
error.


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

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

=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 -----

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]

THANKS! My problem was that I'd selected the whole column when I named the
range. It's been about 4 years since I wrote a formula using SUMPRODUCT()
and i totally forgot about that limitations of not naming whole columns and
the ranges needing to include the same number of rows.

Recreating the named ranges fixed the problem.

Oh, and it WAS a #NUM! ERROR.

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[Emiel Wielinga]

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);D164000))
(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="")

-
Bif
Microsoft Excel MV

"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/tutorial...9-c0b45fb68b78/creating-a-wpf-custom-con.aspx

 

[T. Valko]

=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