I am trying to figure out why sumproduct is suddenly returning #value!

At JE McGimpsey's suggestion, I have been using the following formula
as a double check on a spread sheet
=SUMPRODUCT(ROUND($F$63:$F$549*L63:L549,0)), it was working
wonderfully
this double-checks the sum of column M, which contains
=ROUND((F108*L108),0)
Column F & Column L have several text headers within the range. When
I use Tools/Formula Auditing/Evaluate formula, the #value! error
occurs at those text headers. The odd thing is, the spreadsheets I
was using in Office XP had the exact same info, and didn't have an
error when encountering text. I would just go back to the old
spreadsheet, but it is corrupted. Excel help said to move all the old
data into a new 2003 workbook.

http://support.microsoft.com/kb/820712/en-us

Now sumproduct is returning #value!. What am I doing wrong?

Column F & Column L have several text headers within the range.
When I use Tools/Formula Auditing/Evaluate formula, the #value!
error occurs at those text headers.

As it should.

text * 10 = #VALUE!
10 * text = #VALUE!
text * text = #VALUE!

Try this array formula** :

=SUM(IF(ISNUMBER(F63:F549*L63:L549),ROUND(F63:F549 *L63:L549,0)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"mhoffmeier" wrote in message
ups.com...
I am trying to figure out why sumproduct is suddenly returning #value!

At JE McGimpsey's suggestion, I have been using the following formula
as a double check on a spread sheet
=SUMPRODUCT(ROUND($F$63:$F$549*L63:L549,0)), it was working
wonderfully
this double-checks the sum of column M, which contains
=ROUND((F108*L108),0)
Column F & Column L have several text headers within the range. When
I use Tools/Formula Auditing/Evaluate formula, the #value! error
occurs at those text headers. The odd thing is, the spreadsheets I
was using in Office XP had the exact same info, and didn't have an
error when encountering text. I would just go back to the old
spreadsheet, but it is corrupted. Excel help said to move all the old
data into a new 2003 workbook.

http://support.microsoft.com/kb/820712/en-us

Now sumproduct is returning #value!. What am I doing wrong?

On Jul 31, 1:58 pm, "T. Valko" wrote:
Column F & Column L have several text headers within the range.
When I use Tools/Formula Auditing/Evaluate formula, the #value!
error occurs at those text headers.

As it should.

text * 10 = #VALUE!
10 * text = #VALUE!
text * text = #VALUE!

Try this array formula** :

=SUM(IF(ISNUMBER(F63:F549*L63:L549),ROUND(F63:F549 *L63:L549,0)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP

"mhoffmeier" wrote in message

ups.com...



I am trying to figure out why sumproduct is suddenly returning #value!

At JE McGimpsey's suggestion, I have been using the following formula
as a double check on a spread sheet
=SUMPRODUCT(ROUND($F$63:$F$549*L63:L549,0)), it was working
wonderfully
this double-checks the sum of column M, which contains
=ROUND((F108*L108),0)
Column F & Column L have several text headers within the range. When
I use Tools/Formula Auditing/Evaluate formula, the #value! error
occurs at those text headers. The odd thing is, the spreadsheets I
was using in Office XP had the exact same info, and didn't have an
error when encountering text. I would just go back to the old
spreadsheet, but it is corrupted. Excel help said to move all the old
data into a new 2003 workbook.

http://support.microsoft.com/kb/820712/en-us

Now sumproduct is returning #value!. What am I doing wrong?- Hide quoted text -

- Show quoted text -

Wow, that works great. I'll have to study up on arrays. I still
don't understand why the error did not appear back in Excel 2002

Thanks

"mhoffmeier" wrote in message
ups.com...
On Jul 31, 1:58 pm, "T. Valko" wrote:
Column F & Column L have several text headers within the range.
When I use Tools/Formula Auditing/Evaluate formula, the #value!
error occurs at those text headers.

As it should.

text * 10 = #VALUE!
10 * text = #VALUE!
text * text = #VALUE!

Try this array formula** :

=SUM(IF(ISNUMBER(F63:F549*L63:L549),ROUND(F63:F549 *L63:L549,0)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP

"mhoffmeier" wrote in message

ups.com...



I am trying to figure out why sumproduct is suddenly returning #value!

At JE McGimpsey's suggestion, I have been using the following formula
as a double check on a spread sheet
=SUMPRODUCT(ROUND($F$63:$F$549*L63:L549,0)), it was working
wonderfully
this double-checks the sum of column M, which contains
=ROUND((F108*L108),0)
Column F & Column L have several text headers within the range. When
I use Tools/Formula Auditing/Evaluate formula, the #value! error
occurs at those text headers. The odd thing is, the spreadsheets I
was using in Office XP had the exact same info, and didn't have an
error when encountering text. I would just go back to the old
spreadsheet, but it is corrupted. Excel help said to move all the old
data into a new 2003 workbook.

http://support.microsoft.com/kb/820712/en-us

Now sumproduct is returning #value!. What am I doing wrong?- Hide
quoted text -

- Show quoted text -

Wow, that works great. I'll have to study up on arrays. I still
don't understand why the error did not appear back in Excel 2002

Thanks


Maybe what you were doing back in 2002 wasn't *exactly* the same thing
you're doing now.

Thanks for the feedback!

--
Biff
Microsoft Excel MVP