#### Text in column causing SUMPRODUCT error

```Greegings.  I have a SUMPRODUCT formula that is having errors when one of the
columns has text instead of a NULL or a number.  If I delete the text cells
in that column it works as desired.  I'll give a simple example.  Suppose I
have the following in A1:B6....

a	1
a	2
a	abc
b	1
b	1
a	2

And I need this...

=SUMPRODUCT((\$A\$1:\$A\$6="a")*(\$B\$1:\$B\$6))

It errors out until I delete the "abc" in cell B3, then it works as desired.
I tried to replace the "abc" with a 0 by trying this...

=IF(ISNUMBER(B3)=FALSE,0,B3)

And it works for that particular cell, but it does not work in the
SUMPRODUCT formula like this...

=SUMPRODUCT((\$A\$1:\$A\$6="a")*(IF(ISNUMBER(\$B\$1:\$B\$6)=FALSE,0,\$B\$1:\$B\$6))*(\$B\$1:\$B\$6))

Any ideas on how I can deal with the text cells in column "B"?  Deleting
them is not an option.  Thank you.

```
 0
Utf
1/6/2010 3:17:01 PM
excel.programming 6508 articles. 2 followers.

7 Replies
963 Views

Similar Articles

[PageSpeed] 4

```try:

=SUMPRODUCT(--(\$A\$1:\$A\$6="a"),\$B\$1:\$B\$6)

Just like =sum() will treat text as 0, =sumproduct() with the comma syntax will
do the same.

Adjust the ranges to match--but you can't use whole columns (except in xl2007+).

=sumproduct() likes to work with numbers.  The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

Greg Snidow wrote:
>
> Greegings.  I have a SUMPRODUCT formula that is having errors when one of the
> columns has text instead of a NULL or a number.  If I delete the text cells
> in that column it works as desired.  I'll give a simple example.  Suppose I
> have the following in A1:B6....
>
> a       1
> a       2
> a       abc
> b       1
> b       1
> a       2
>
> And I need this...
>
> =SUMPRODUCT((\$A\$1:\$A\$6="a")*(\$B\$1:\$B\$6))
>
> It errors out until I delete the "abc" in cell B3, then it works as desired.
>  I tried to replace the "abc" with a 0 by trying this...
>
> =IF(ISNUMBER(B3)=FALSE,0,B3)
>
> And it works for that particular cell, but it does not work in the
> SUMPRODUCT formula like this...
>
> =SUMPRODUCT((\$A\$1:\$A\$6="a")*(IF(ISNUMBER(\$B\$1:\$B\$6)=FALSE,0,\$B\$1:\$B\$6))*(\$B\$1:\$B\$6))
>
> Any ideas on how I can deal with the text cells in column "B"?  Deleting
> them is not an option.  Thank you.

--

Dave Peterson
```
 0
Dave
1/6/2010 3:33:40 PM
```This array-entered** formula appears to do what you want...

=SUM((\$A\$1:\$A\$6="a")*IF(ISNUMBER(\$B\$1:\$B\$6),\$B\$1:\$B\$6,0))

**Commit formula using Ctrl+Shift+Enter and not just Enter by itself

--
Rick (MVP - Excel)

"Greg Snidow" <GregSnidow@discussions.microsoft.com> wrote in message
news:3DCA3F3B-6D5E-4268-8DFB-17B1F9DEA910@microsoft.com...
> Greegings.  I have a SUMPRODUCT formula that is having errors when one of
> the
> columns has text instead of a NULL or a number.  If I delete the text
> cells
> in that column it works as desired.  I'll give a simple example.  Suppose
> I
> have the following in A1:B6....
>
> a 1
> a 2
> a abc
> b 1
> b 1
> a 2
>
> And I need this...
>
> =SUMPRODUCT((\$A\$1:\$A\$6="a")*(\$B\$1:\$B\$6))
>
> It errors out until I delete the "abc" in cell B3, then it works as
> desired.
> I tried to replace the "abc" with a 0 by trying this...
>
> =IF(ISNUMBER(B3)=FALSE,0,B3)
>
> And it works for that particular cell, but it does not work in the
> SUMPRODUCT formula like this...
>
> =SUMPRODUCT((\$A\$1:\$A\$6="a")*(IF(ISNUMBER(\$B\$1:\$B\$6)=FALSE,0,\$B\$1:\$B\$6))*(\$B\$1:\$B\$6))
>
> Any ideas on how I can deal with the text cells in column "B"?  Deleting
> them is not an option.  Thank you.
>
>
>
>
>

```
 0
Rick
1/6/2010 3:37:21 PM
```Try changing you formula to this

=SUMPRODUCT(--(\$A\$1:\$A\$6="a"),\$B\$1:\$B\$6)

The double operator -- forces an evaluation and replacing the * with a ,
forces each array to evaluate seperately
--
If this helps, please remember to click yes.

"Greg Snidow" wrote:

> Greegings.  I have a SUMPRODUCT formula that is having errors when one of the
> columns has text instead of a NULL or a number.  If I delete the text cells
> in that column it works as desired.  I'll give a simple example.  Suppose I
> have the following in A1:B6....
>
> a	1
> a	2
> a	abc
> b	1
> b	1
> a	2
>
> And I need this...
>
> =SUMPRODUCT((\$A\$1:\$A\$6="a")*(\$B\$1:\$B\$6))
>
> It errors out until I delete the "abc" in cell B3, then it works as desired.
>  I tried to replace the "abc" with a 0 by trying this...
>
> =IF(ISNUMBER(B3)=FALSE,0,B3)
>
> And it works for that particular cell, but it does not work in the
> SUMPRODUCT formula like this...
>
> =SUMPRODUCT((\$A\$1:\$A\$6="a")*(IF(ISNUMBER(\$B\$1:\$B\$6)=FALSE,0,\$B\$1:\$B\$6))*(\$B\$1:\$B\$6))
>
> Any ideas on how I can deal with the text cells in column "B"?  Deleting
> them is not an option.  Thank you.
>
>
>
>
>
```
 0
Utf
1/6/2010 4:06:02 PM
```Please note, I am trying all of your suggestions, with Paul's and Dave's
being the same, so  I am replying to myself simply to consolidate the thread.
First off, this is the real formula from my spreadsheet, and the one giving
me errors...

=SUMPRODUCT((\$A\$23:\$A\$1604="o")*(\$B\$23:\$B\$1604="c")*(\$E\$23:\$E\$1604="Best
View-Current (SFU)")*(F\$23:F\$1604))

The final array, F\$23:F\$1604, is the one in question, where some of the
cells have text values.  This is what I have tried so far...

Added dashes, and replaced '*' with ','
=SUMPRODUCT(--(\$A\$23:\$A\$1604="o"),(\$B\$23:\$B\$1604="c"),(\$E\$23:\$E\$1604="Best
View-Current (SFU)"),(F\$23:F\$1604))
This got rid of the error, but now returns a 0, where the answer when I
delete the text cells is around 250,000.

Tried entering it as an array formula by using ctrl+shift+enter, still with
the dashes and ',' instead of '*'
{=SUMPRODUCT(--(\$A\$23:\$A\$1604="o"),(\$B\$23:\$B\$1604="c"),(\$E\$23:\$E\$1604="Best
View-Current (SFU)"),IF(ISNUMBER(F\$23:F\$1604)=FALSE,0,F\$23:F\$1604))}
And the answer is again 0.

Tried entering it as an array formula without the dashes and with '*''s
instead of ',''s...
{=SUMPRODUCT((\$A\$23:\$A\$1604="o")*(\$B\$23:\$B\$1604="c")*(\$E\$23:\$E\$1604="Best
View-Current (SFU)")*IF(ISNUMBER(F\$23:F\$1604)=FALSE,0,F\$23:F\$1604))}

And the answer was correct, so, Rick, thanks for the tip.

Paul and Dave,  I'm not sure exactly what the dashes are doing, nor why it
did not work in my case.  Do they have a technical name I could use to google
more about it?

Thank you all, for taking time out of your day to consider my problem and
reply so promptly.

Greg

"Greg Snidow" wrote:

> Greegings.  I have a SUMPRODUCT formula that is having errors when one of the
> columns has text instead of a NULL or a number.  If I delete the text cells
> in that column it works as desired.  I'll give a simple example.  Suppose I
> have the following in A1:B6....
>
> a	1
> a	2
> a	abc
> b	1
> b	1
> a	2
>
> And I need this...
>
> =SUMPRODUCT((\$A\$1:\$A\$6="a")*(\$B\$1:\$B\$6))
>
> It errors out until I delete the "abc" in cell B3, then it works as desired.
>  I tried to replace the "abc" with a 0 by trying this...
>
> =IF(ISNUMBER(B3)=FALSE,0,B3)
>
> And it works for that particular cell, but it does not work in the
> SUMPRODUCT formula like this...
>
> =SUMPRODUCT((\$A\$1:\$A\$6="a")*(IF(ISNUMBER(\$B\$1:\$B\$6)=FALSE,0,\$B\$1:\$B\$6))*(\$B\$1:\$B\$6))
>
> Any ideas on how I can deal with the text cells in column "B"?  Deleting
> them is not an option.  Thank you.
>
>
>
>
>
```
 0
Utf
1/6/2010 6:06:01 PM
```Try:

=SUMPRODUCT(--(\$A\$23:\$A\$1604="o"),
--(\$B\$23:\$B\$1604="c"),
--(\$E\$23:\$E\$1604="Best View-Current (SFU)"),
(F\$23:F\$1604))

Adjust the ranges to match--but you can't use whole columns (except in xl2007+).

=sumproduct() likes to work with numbers.  The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

====================
A23:A1604="o"
will result in an array of 1582 true/falses.  The -- stuff changes the true's to
1's and the false's to 0.

Put
TRUE
in A1

put
-a1
in B1

put
--a1
in C1

And you'll see why you want two of them.

Greg Snidow wrote:
>
> Please note, I am trying all of your suggestions, with Paul's and Dave's
> being the same, so  I am replying to myself simply to consolidate the thread.
>  First off, this is the real formula from my spreadsheet, and the one giving
> me errors...
>
> =SUMPRODUCT((\$A\$23:\$A\$1604="o")*(\$B\$23:\$B\$1604="c")*(\$E\$23:\$E\$1604="Best
> View-Current (SFU)")*(F\$23:F\$1604))
>
> The final array, F\$23:F\$1604, is the one in question, where some of the
> cells have text values.  This is what I have tried so far...
>
> Added dashes, and replaced '*' with ','
> =SUMPRODUCT(--(\$A\$23:\$A\$1604="o"),(\$B\$23:\$B\$1604="c"),(\$E\$23:\$E\$1604="Best
> View-Current (SFU)"),(F\$23:F\$1604))
> This got rid of the error, but now returns a 0, where the answer when I
> delete the text cells is around 250,000.
>
> Tried entering it as an array formula by using ctrl+shift+enter, still with
> the dashes and ',' instead of '*'
> {=SUMPRODUCT(--(\$A\$23:\$A\$1604="o"),(\$B\$23:\$B\$1604="c"),(\$E\$23:\$E\$1604="Best
> View-Current (SFU)"),IF(ISNUMBER(F\$23:F\$1604)=FALSE,0,F\$23:F\$1604))}
> And the answer is again 0.
>
> Tried entering it as an array formula without the dashes and with '*''s
> instead of ',''s...
> {=SUMPRODUCT((\$A\$23:\$A\$1604="o")*(\$B\$23:\$B\$1604="c")*(\$E\$23:\$E\$1604="Best
> View-Current (SFU)")*IF(ISNUMBER(F\$23:F\$1604)=FALSE,0,F\$23:F\$1604))}
>
> And the answer was correct, so, Rick, thanks for the tip.
>
> Paul and Dave,  I'm not sure exactly what the dashes are doing, nor why it
> did not work in my case.  Do they have a technical name I could use to google
> more about it?
>
> Thank you all, for taking time out of your day to consider my problem and
> reply so promptly.
>
> Greg
>
> "Greg Snidow" wrote:
>
> > Greegings.  I have a SUMPRODUCT formula that is having errors when one of the
> > columns has text instead of a NULL or a number.  If I delete the text cells
> > in that column it works as desired.  I'll give a simple example.  Suppose I
> > have the following in A1:B6....
> >
> > a     1
> > a     2
> > a     abc
> > b     1
> > b     1
> > a     2
> >
> > And I need this...
> >
> > =SUMPRODUCT((\$A\$1:\$A\$6="a")*(\$B\$1:\$B\$6))
> >
> > It errors out until I delete the "abc" in cell B3, then it works as desired.
> >  I tried to replace the "abc" with a 0 by trying this...
> >
> > =IF(ISNUMBER(B3)=FALSE,0,B3)
> >
> > And it works for that particular cell, but it does not work in the
> > SUMPRODUCT formula like this...
> >
> > =SUMPRODUCT((\$A\$1:\$A\$6="a")*(IF(ISNUMBER(\$B\$1:\$B\$6)=FALSE,0,\$B\$1:\$B\$6))*(\$B\$1:\$B\$6))
> >
> > Any ideas on how I can deal with the text cells in column "B"?  Deleting
> > them is not an option.  Thank you.
> >
> >
> >
> >
> >

--

Dave Peterson
```
 0
Dave
1/6/2010 6:45:01 PM
```Dave, that formula also works.  I think I did not fully understand the use of
the dashes, so I only had them in front of the first array.  I like this
method better, since it eliminates the use of an array formula, which could
easily be messed up by users not understanding that you can not just type in
the brackets.  Thanks for the links, they are very informative.

"Dave Peterson" wrote:

> Try:
>
> =SUMPRODUCT(--(\$A\$23:\$A\$1604="o"),
>             --(\$B\$23:\$B\$1604="c"),
>             --(\$E\$23:\$E\$1604="Best View-Current (SFU)"),
>               (F\$23:F\$1604))
>
> Adjust the ranges to match--but you can't use whole columns (except in xl2007+).
>
> =sumproduct() likes to work with numbers.  The -- stuff changes trues and falses
> to 1's and 0's.
>
> Bob Phillips explains =sumproduct() in much more detail here:
> http://www.xldynamic.com/source/xld.SUMPRODUCT.html
>
> And J.E. McGimpsey has some notes at:
> http://mcgimpsey.com/excel/formulae/doubleneg.html
>
> ====================
> A23:A1604="o"
> will result in an array of 1582 true/falses.  The -- stuff changes the true's to
> 1's and the false's to 0.
>
> Put
> TRUE
> in A1
>
> put
> -a1
> in B1
>
> put
> --a1
> in C1
>
> And you'll see why you want two of them.
>
> Greg Snidow wrote:
> >
> > Please note, I am trying all of your suggestions, with Paul's and Dave's
> > being the same, so  I am replying to myself simply to consolidate the thread.
> >  First off, this is the real formula from my spreadsheet, and the one giving
> > me errors...
> >
> > =SUMPRODUCT((\$A\$23:\$A\$1604="o")*(\$B\$23:\$B\$1604="c")*(\$E\$23:\$E\$1604="Best
> > View-Current (SFU)")*(F\$23:F\$1604))
> >
> > The final array, F\$23:F\$1604, is the one in question, where some of the
> > cells have text values.  This is what I have tried so far...
> >
> > Added dashes, and replaced '*' with ','
> > =SUMPRODUCT(--(\$A\$23:\$A\$1604="o"),(\$B\$23:\$B\$1604="c"),(\$E\$23:\$E\$1604="Best
> > View-Current (SFU)"),(F\$23:F\$1604))
> > This got rid of the error, but now returns a 0, where the answer when I
> > delete the text cells is around 250,000.
> >
> > Tried entering it as an array formula by using ctrl+shift+enter, still with
> > the dashes and ',' instead of '*'
> > {=SUMPRODUCT(--(\$A\$23:\$A\$1604="o"),(\$B\$23:\$B\$1604="c"),(\$E\$23:\$E\$1604="Best
> > View-Current (SFU)"),IF(ISNUMBER(F\$23:F\$1604)=FALSE,0,F\$23:F\$1604))}
> > And the answer is again 0.
> >
> > Tried entering it as an array formula without the dashes and with '*''s
> > instead of ',''s...
> > {=SUMPRODUCT((\$A\$23:\$A\$1604="o")*(\$B\$23:\$B\$1604="c")*(\$E\$23:\$E\$1604="Best
> > View-Current (SFU)")*IF(ISNUMBER(F\$23:F\$1604)=FALSE,0,F\$23:F\$1604))}
> >
> > And the answer was correct, so, Rick, thanks for the tip.
> >
> > Paul and Dave,  I'm not sure exactly what the dashes are doing, nor why it
> > did not work in my case.  Do they have a technical name I could use to google
> > more about it?
> >
> > Thank you all, for taking time out of your day to consider my problem and
> > reply so promptly.
> >
> > Greg
> >
> > "Greg Snidow" wrote:
> >
> > > Greegings.  I have a SUMPRODUCT formula that is having errors when one of the
> > > columns has text instead of a NULL or a number.  If I delete the text cells
> > > in that column it works as desired.  I'll give a simple example.  Suppose I
> > > have the following in A1:B6....
> > >
> > > a     1
> > > a     2
> > > a     abc
> > > b     1
> > > b     1
> > > a     2
> > >
> > > And I need this...
> > >
> > > =SUMPRODUCT((\$A\$1:\$A\$6="a")*(\$B\$1:\$B\$6))
> > >
> > > It errors out until I delete the "abc" in cell B3, then it works as desired.
> > >  I tried to replace the "abc" with a 0 by trying this...
> > >
> > > =IF(ISNUMBER(B3)=FALSE,0,B3)
> > >
> > > And it works for that particular cell, but it does not work in the
> > > SUMPRODUCT formula like this...
> > >
> > > =SUMPRODUCT((\$A\$1:\$A\$6="a")*(IF(ISNUMBER(\$B\$1:\$B\$6)=FALSE,0,\$B\$1:\$B\$6))*(\$B\$1:\$B\$6))
> > >
> > > Any ideas on how I can deal with the text cells in column "B"?  Deleting
> > > them is not an option.  Thank you.
> > >
> > >
> > >
> > >
> > >
>
> --
>
> Dave Peterson
> .
>
```
 0
Utf
1/6/2010 8:48:03 PM
```Bob and J.E. did very nice work explaining how this works--and when you should
use the * operand, too!

Greg Snidow wrote:
>
> Dave, that formula also works.  I think I did not fully understand the use of
> the dashes, so I only had them in front of the first array.  I like this
> method better, since it eliminates the use of an array formula, which could
> easily be messed up by users not understanding that you can not just type in
> the brackets.  Thanks for the links, they are very informative.
>
> "Dave Peterson" wrote:
>
> > Try:
> >
> > =SUMPRODUCT(--(\$A\$23:\$A\$1604="o"),
> >             --(\$B\$23:\$B\$1604="c"),
> >             --(\$E\$23:\$E\$1604="Best View-Current (SFU)"),
> >               (F\$23:F\$1604))
> >
> > Adjust the ranges to match--but you can't use whole columns (except in xl2007+).
> >
> > =sumproduct() likes to work with numbers.  The -- stuff changes trues and falses
> > to 1's and 0's.
> >
> > Bob Phillips explains =sumproduct() in much more detail here:
> > http://www.xldynamic.com/source/xld.SUMPRODUCT.html
> >
> > And J.E. McGimpsey has some notes at:
> > http://mcgimpsey.com/excel/formulae/doubleneg.html
> >
> > ====================
> > A23:A1604="o"
> > will result in an array of 1582 true/falses.  The -- stuff changes the true's to
> > 1's and the false's to 0.
> >
> > Put
> > TRUE
> > in A1
> >
> > put
> > -a1
> > in B1
> >
> > put
> > --a1
> > in C1
> >
> > And you'll see why you want two of them.
> >
> > Greg Snidow wrote:
> > >
> > > Please note, I am trying all of your suggestions, with Paul's and Dave's
> > > being the same, so  I am replying to myself simply to consolidate the thread.
> > >  First off, this is the real formula from my spreadsheet, and the one giving
> > > me errors...
> > >
> > > =SUMPRODUCT((\$A\$23:\$A\$1604="o")*(\$B\$23:\$B\$1604="c")*(\$E\$23:\$E\$1604="Best
> > > View-Current (SFU)")*(F\$23:F\$1604))
> > >
> > > The final array, F\$23:F\$1604, is the one in question, where some of the
> > > cells have text values.  This is what I have tried so far...
> > >
> > > Added dashes, and replaced '*' with ','
> > > =SUMPRODUCT(--(\$A\$23:\$A\$1604="o"),(\$B\$23:\$B\$1604="c"),(\$E\$23:\$E\$1604="Best
> > > View-Current (SFU)"),(F\$23:F\$1604))
> > > This got rid of the error, but now returns a 0, where the answer when I
> > > delete the text cells is around 250,000.
> > >
> > > Tried entering it as an array formula by using ctrl+shift+enter, still with
> > > the dashes and ',' instead of '*'
> > > {=SUMPRODUCT(--(\$A\$23:\$A\$1604="o"),(\$B\$23:\$B\$1604="c"),(\$E\$23:\$E\$1604="Best
> > > View-Current (SFU)"),IF(ISNUMBER(F\$23:F\$1604)=FALSE,0,F\$23:F\$1604))}
> > > And the answer is again 0.
> > >
> > > Tried entering it as an array formula without the dashes and with '*''s
> > > instead of ',''s...
> > > {=SUMPRODUCT((\$A\$23:\$A\$1604="o")*(\$B\$23:\$B\$1604="c")*(\$E\$23:\$E\$1604="Best
> > > View-Current (SFU)")*IF(ISNUMBER(F\$23:F\$1604)=FALSE,0,F\$23:F\$1604))}
> > >
> > > And the answer was correct, so, Rick, thanks for the tip.
> > >
> > > Paul and Dave,  I'm not sure exactly what the dashes are doing, nor why it
> > > did not work in my case.  Do they have a technical name I could use to google
> > > more about it?
> > >
> > > Thank you all, for taking time out of your day to consider my problem and
> > > reply so promptly.
> > >
> > > Greg
> > >
> > > "Greg Snidow" wrote:
> > >
> > > > Greegings.  I have a SUMPRODUCT formula that is having errors when one of the
> > > > columns has text instead of a NULL or a number.  If I delete the text cells
> > > > in that column it works as desired.  I'll give a simple example.  Suppose I
> > > > have the following in A1:B6....
> > > >
> > > > a     1
> > > > a     2
> > > > a     abc
> > > > b     1
> > > > b     1
> > > > a     2
> > > >
> > > > And I need this...
> > > >
> > > > =SUMPRODUCT((\$A\$1:\$A\$6="a")*(\$B\$1:\$B\$6))
> > > >
> > > > It errors out until I delete the "abc" in cell B3, then it works as desired.
> > > >  I tried to replace the "abc" with a 0 by trying this...
> > > >
> > > > =IF(ISNUMBER(B3)=FALSE,0,B3)
> > > >
> > > > And it works for that particular cell, but it does not work in the
> > > > SUMPRODUCT formula like this...
> > > >
> > > > =SUMPRODUCT((\$A\$1:\$A\$6="a")*(IF(ISNUMBER(\$B\$1:\$B\$6)=FALSE,0,\$B\$1:\$B\$6))*(\$B\$1:\$B\$6))
> > > >
> > > > Any ideas on how I can deal with the text cells in column "B"?  Deleting
> > > > them is not an option.  Thank you.
> > > >
> > > >
> > > >
> > > >
> > > >
> >
> > --
> >
> > Dave Peterson
> > .
> >

--

Dave Peterson
```
 0
Dave
1/6/2010 10:42:25 PM
 Reply:

Similar Artilces:

GP 10.0 home page Runtime Error
Hello: This client was getting the "The specified module could not be found error", upon logging into GP 10.0 for the first time after a new install. I remedied this home page issue, by registering the msxml2.dll file in C:\WINNT\System32. Finally, this got the home page to at least appear. But, now we get this Runtime Error that says "Class not registered". We have installed the XML Parser and the Office 2003 Web Components and have registered the msxml3.dll file. No success. Any ideas? Security is ridiculously tight a this client. Would it do any good to mes...

Custom view error
I cannot determine why I get this error when I try to switch to a view I just created. There's nothing special going on in the worksheet. It's just a list which I have applied an Advanced Filter to, but which hides all non- matching data. Is the Advanced Filter not compatible with Custom Views? Thanks for any help you can offer. Kimberly ...

Query to text file cuts my leading zeros off.
Hello, this is my third attempt to post this question. I have a query that has a value of "012810" in one of the fields. When I run the query it shows up as 012810. When I export to excell it shows up as 012810. When I export to text file it always cuts off the zero and I get 12810. If I change the value to say 212810 and then export to a text file it works fine and returns 212810. Why is the zero being chopped off during a text file export? Thank you, VADIMBAR Its being treated as a numeric value. Is this column defined as numeric or text? It needs to be defin...

Excel file in use error
This error occurs with spreadsheets which contain macros, they cannot be ammended nor deleted by a network administrator! any ideas???? ...

DELETE column in TABLE
Hi all, Is it possible to create a query that DELETES column in a table in ACCESS 2007? the reason is, because I receive a very big database from my supplier, I need to delete a large number of column that are not needed by us. now there are 200 fields and i only need 15. when publishing to complete database it takes to much time and is way to big in file Size. regards, Geert GoodLook wrote: >Hi all, > >Is it possible to create a query that DELETES column in a table in ACCESS >2007? >the reason is, because I receive a very big database from my supplier,...

Evalutation Software Error on Old data
Hello, When I attempt to open the manager connected to an old database (i.e. a copy from a backup months or years ago.), I get an error saying the evaluation copy of the software has expired, and the program shuts down. Using version 1.3.1011 Thanks, Greg Yep, The DB is older than 45 days and therefore will not let you access it. If you create a new DB you can use it. =========== "Greg Shepherd" <greg@frgusa.com> wrote in message news:%23sgkBF7VIHA.2000@TK2MSFTNGP05.phx.gbl... > Hello, > > When I attempt to open the manager connected to an old database (i.e. >...

Invoice payment error
I had submitted this question a few days ago with no response, so I'm resubmitting with more information. There is an invoice showing a balance due on the aged trail balance report. However, when a document inquiry is run for this invoice, it is showing paid in full. I found the transaction in the RM20201 table (Receivables Open Transaction File) with a 0 in the Posted field. It's the only record in that table with that value. What can I do to resolve this? This transaction is from a prior year. Thanks for any help you can give! daw Usually, running Reconcile will correc...

Outlook and "Bad Image" error (msimtf.dll)
I have a user in the office, with Windows XP Home (SP2) and Office 2002 (SP3). She's starting to get this message: "OUTLOOK.EXE - Bad Image. The application or DLL C:\WINDOWS \System32\msimtf.dll is not a valid Windows image. Please check this against your installation diskette." every time she starts up Outlook, sometimes two or three of them in a row she has to OK. They'll also pop up now and then while using Outlook. This only started happening a couple of days ago, and she hasn't installed or uninstalled any software lately, and nothing else unusual has happened wit...

Sumproduct Help!
Hi Thanks for looking Below is the formula i'm using and it works just fine. =SUMPRODUCT(--(YEAR(Serial!\$G\$2:\$G\$3222)=\$B\$1),--(MONTH(Serial!\$G\$2:\$G\$3222)=D2) What i'm trying to do is select the whole column (G) as below =SUMPRODUCT(--(YEAR(Serial!G:G)=\$B\$1),--(MONTH(Serial!G:G)=D2) I just can't get this to work, any ideas Cheers Peter (UK) On Sun, 15 Mar 2009 08:00:49 -0000, "Peter" <no@spam.co.uk> wrote: >Hi >Thanks for looking > >Below is the formula i'm using and it works just fine. >=SUMPRODUCT(--(YEAR(Serial!\$G\$2:\$G\$3222)=\$B\$1),--(M...

dynamic chart title text
Is there a way to link text in a chart title to a cell so that th contents of the cell are displayed as the chart title -- cwinter ----------------------------------------------------------------------- cwinters's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1838 View this thread: http://www.excelforum.com/showthread.php?threadid=37783 Select the title then in the formula bar hit = and select the cell you want to use. "cwinters" wrote: > > Is there a way to link text in a chart title to a cell so that the > contents of the cell are disp...

Paste Special, Text in Excel 2007
I'm copying data from a web site to an Excel 2007 worksheet. Specifically, I'm creating a database of my DVDs and using IMDB as a source of information, specifically the release date of the movie. Right now I'm copying the date from the web page, right-clicking on the appropriate cell and selecting Paste Special and then selecting Text. This is getting to be too repetitive to be convenient. Is there a keyboard shortcut to do this? I'd love to just hit Ctrl-V (or something similar) to paste it as plain text with no HTML codes or formatting. -- "Pinky, are you pon...

Can you add a field in the body of a text box
I'm trying to use a REPORT in Access to generate a letter. Can I have a field display in the body of the text box? For ex: if my field is [total] can I have something like "Our records indicate your total purchases are [total] and your total expenses are..." thanks! The answer to your question is NO! However you can do what you want by storing phrase in a table: TblPhrase PhraseID Phrase Where the first Phrase record is "Our records indicate your total purchases are" and the second Phrase record is "and your total expenses are...". The...

DPM 2010 Setup error
During Installation of DPM setup I get the following error: DPMSetup has stopped working Problem details: Problem Signature Problem Event name: DPMException Problem Signature 01: DPMSetup Problem Signature 02: 3.0.7336.0 Problem Signature 03: SetupDpm.exe Problem Signature 04: 3.0.7336.0 Problem Signature 05: System.UnauthorizedAccessException Problem Signature 06: System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal Problem Signature 07: CC0B98E5 OS Version: 6.1.7600.2.0.0.272.7 Local ID: 1030 This problem occurs approx 10 seconds afther the DPM setup start...

Windows Live Messenger: Logs Unspecified Error When Saving/Deletin
I have been having issues trying to save or delete logs from a friends conversation. Everytime I close the conversation, the screen pops up asking me to save older messages or delete, and choosing either of them pops up another screen saying Unspecified Error. Is there any solution to this, I have been kinda annoyed by this. Link to box that pops up: http://i453.photobucket.com/albums/qq254/Areno/Untitled-1.jpg This is the screen that shows up after i try saving or deleting. Greetings, You'll get this message if it can't parse the XML - for instance, if some binary...

Retrieve unique items with 2 criteria (USING SUMPRODUCT)
This question was posted before but the solution proposed used different functions. I would like to insist. Is there a modification to the formula below, using SUMPRODUCT, that allows to retrieve the number of unique itens that meet a 2nd criteria? Original formula: =SUMPRODUCT((A1:A20<>"")/COUNTIF(A1:A20,A1:A20&"")) In my case, I need the numbers of unique branches (column B) that meet a specifc criteria in another column (M). So, I have the formula: =SUMPRODUCT((all!\$B\$2:\$B\$5514<>"")/COUNTIF(all!\$B\$2:\$B\$5514,all!\$B\$2:\$B\$5514&...

Can I change text in Pop-Up Boxes for chart items?
I know that I can change the data labels, but I have hundreds of items in the xy chart and want to be able to scroll over a xy data point and have text show instead of the xy coordinates. Thanks! Hi mike, Check out Tushar Metha's Hover Chart Label addin. http://www.tushar-mehta.com/ Cheers Andy mike wrote: > I know that I can change the data labels, but I have > hundreds of items in the xy chart and want to be able to > scroll over a xy data point and have text show instead of > the xy coordinates. Thanks! -- Andy Pope, Microsoft MVP - Excel http://www.andypope....

DPM error 43 and 53!
Hello, I am at my end trying to correct this error. I am running DPM2007SP1 on Windows2K3SP1 box. I am patched to the latest as well. I have managed to work my way through most of my daily DPM errors, however this error has been dogging me daily for a week. This particular PG is backing up 5 Hyper-V servers on a single host. The host is 2008R2 and a Dell M950 blade. The 5 Hyper-V's are a Hyperion development environment and have quite a bit of traffic. I setup this PG about a week ago and was successful in building 4 of 5 replicas. The 5th continued to give me error 43....

Title case for text in columns
> This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3216882494_408470 Content-type: text/plain; charset="US-ASCII" Content-transfer-encoding: 7bit Hi formatting gurus, How can you apply title case to text in columns in excel?? Please help. Annalise --B_3216882494_408470 Content-type: text/html; charset="US-ASCII" Content-transfer-encoding: quoted-printable <HTML> <HEAD> <TITLE>Title case for text in columns</TITLE> </HEAD> <BODY> <FONT...

Error: "customizing security roles" while installing CRM SBE on SB
Just wanted to make sure this could be found in the newsgroup... There is a known bug when installing Microsoft Dynamics CRM 3.0 Small Business on Microsoft Small Business Server R2 Premium (whcih ships with Microsoft SQL Server 2005 Workgroup) when SP2 of SQL Server 2005 is applied. Symptoms: Setup failed to validate specified Reporting Services Report Server http://Servername/reportserver. Error: The feature: “Customizing security roles” is not supported in this edition of Reporting Services. The knowledge base reference can be found here: http://support.microsoft.com/default.aspx/kb...

sndPlaySound Link error
Hi, I wrote an application in VC++ 7.0 which uses sndPlaySound method .But when i am building to make a setup in Release then i am getting link error DCS error LNK2019: unresolved external symbol __imp__sndPlaySoundA@8 referenced in function "protected: virtual void __thiscall CAlarmDlg::OnOK(void)" (?OnOK@CAlarmDlg@@MAEXXZ) can any one help me out on this.I could able to build if it is in Debug Mode. Reny >I wrote an application in VC++ 7.0 which uses sndPlaySound method .But when >i am building to make a setup in Release then i am getting link error > >DCS...

how do I angle text without angling border?
I've rotated text in a cell and it rotates my border as well. How can I keep my border from rotating. I don't believe you can. A work around would be to eliminate the norma border entirely and replace it with a box object from the drawin toolbar. Be sure to format it empty (no-fill) :cool -- ElsiePO ----------------------------------------------------------------------- ElsiePOA's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=390 View this thread: http://www.excelforum.com/showthread.php?threadid=26281 ...

Converting currency to text and spliting to two lines
I have mangaged trough a google search to get the code to convert currency in cell to text in another for printing cheques etc. The problem is that once the text exceeds 40 characters i need it to go onto another line. Can someone please advise how i can split this at a space if more than 40 characters and put second half in a specific cell. eg line one of text is in cell B2 and Line two in cell B4 so Cell B2 would say "Five Hundred and Sixty Five Pounds and Twenty" and cell b4 would say "five Pence" The conversion code is below. '*******...

Serial Number on Receipts
Can anyone tell me what element needs to be included to print the serial number on the 40 column receipt. Thanks Remove the tilde (~) from the Alignment line in the Transaction Details part of the receipt that you want to modify. To do this, follow these steps:1. Right-click the Receipt.xml file, and then click Copy. 2. Move the copy of Receipt.xml into another folder as a backup. Receipt files are located in your Store Operations installation folder. By default, the installation folder has the following location. C:\Program Files\Microsoft Retail Management System\Store Operations\...

Error when searching in message tracking, c1032751
Hello, The scenario is this: - Exchange 2k3 active/passive 2 node cluster. - Fresh off of a migration. All mailboxes are migrated from an Exch 2k cluster. All are working great. - Get an error saying 'The tracking database on server.domain.com is not available...' error c1032751. This happens whenever you go into the System Manager and try to do a search on anything. -Tracking is enabled via policy. -logs are gathering in the 'servername.log' share without issue. The share is on a shared drive resource for the cluster. -i have cleared the logs once. This has had no impa...

FRx 6.7 install error
We upgraded to GP v8 about a week ago, everything is working ok now other than FRx on one Windows 2000 workstation. During the install I get: "Error Generated: -2147219990 - Couldn't Write Banner" There's a KB article on Customer Source with the same error that pertains to Solomon regarding an unregistered dao350.dll. I tried to register the dll but I still get the same error. Anyone run into this? I'm experiencing that problem right now, just doing a test install on a Windows 2000 Server machine. The KB article that you referenced didn't help me either. I ...