SUMPRODUCT() yielding #NUM!

A

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!
 
G

Glenn

Ann said:
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!
Click to expand...

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

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.
 
J

Joe User

Ann Scharpf said:
=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.
Click to expand...

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.
Click to expand...

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

T. Valko

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

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

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

--
Biff
Microsoft Excel MVP


Joe User said:
Ann Scharpf said:
=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.
Click to expand...

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.
Click to expand...

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 said:
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!
Click to expand...
Click to expand...
 
J

Joe User

T. Valko said:
I don't have Excel 2003 but in Excel 2002 this returns #NUM!:
=SUMPRODUCT(--(A:A=""))
Click to expand...

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

T. Valko said:
To my knowledge, SUMPRODUCT does not return #NUM.
At least, that is the case for Excel 2003
Click to expand...

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

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

--
Biff
Microsoft Excel MVP


Joe User said:
Ann Scharpf said:
=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.
Click to expand...

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.
Click to expand...

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 said:
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!
Click to expand...
Click to expand...
Click to expand...
 
A

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.
 
E

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

-
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Getting #NUM! result for SumProduct 10
Using NAMES in SUMPRODUCT giving error 2
Problem with sumproduct, countif functions.. 2
Count dates for previous month 3
#REF Error in Named Range with INDIRECT, SUMPRODUCT, and OFFSET 6
Using COUNTIF or SUMPRODUCT for Year 3
Two questions-SUMPRODUCT & IF something 5
sumif vs sumproduct question 4

Top