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

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!



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

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

--
Biff
Microsoft Excel MVP


"Ann Scharpf" wrote in 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

"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

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

"T. Valko" wrote:
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=""))

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" wrote in 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


"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

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.
--
Ann Scharpf


"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"Ann Scharpf" wrote in 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


.

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Ann Scharpf" wrote in message
...
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.
--
Ann Scharpf


"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"Ann Scharpf" wrote in 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


.

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

--
Biff
Microsoft Excel MVP


"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/tutorials...ustom-con.aspx

=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

--
Biff
Microsoft Excel MVP


<Emiel Wielinga wrote in message
...
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=""))

--
Biff
Microsoft Excel MVP


"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/tutorials...ustom-con.aspx