From: Nadine on
I copied the formula into my worksheet and changed A1:A111 to M:M and the
result is #NUM. Is this due to the fact that my invoice numbers are
alpha-numeric? Thanks.

"Bob Umlas, Excel MVP" wrote:

> The formula can't produce #NUM unless the range already has a #NUM in it. Are
> you sure you entered it as written?
>
> "Nadine" wrote:
>
> > I get the #NUM result. Thanks for trying.
> >
> > "Bob Umlas, Excel MVP" wrote:
> >
> > > =SUMPRODUCT(N(A1:A111<>""))
> > > Bob Umlas
> > > Excel MVP
> > >
> > > "Nadine" wrote:
> > >
> > > > I have a column that has about 111 rows to it. In each row is a formula that
> > > > will find an invoice number if it exists elsewhere. The are alpha-numeric
> > > > invoice #s.
> > > >
> > > > I need to count the number of invoices found. If I use the COUNTA formula
> > > > for that column it will count those cells that have the fomula in it but no
> > > > result. I only want to count those cells that have a result from the
> > > > formula. Any ideas for using Excel 2003? Thanks.
From: Dave Peterson on
xl2007 is the first version that allows you to use the entire column.

Nadine wrote:
>
> I copied the formula into my worksheet and changed A1:A111 to M:M and the
> result is #NUM. Is this due to the fact that my invoice numbers are
> alpha-numeric? Thanks.
>
> "Bob Umlas, Excel MVP" wrote:
>
> > The formula can't produce #NUM unless the range already has a #NUM in it. Are
> > you sure you entered it as written?
> >
> > "Nadine" wrote:
> >
> > > I get the #NUM result. Thanks for trying.
> > >
> > > "Bob Umlas, Excel MVP" wrote:
> > >
> > > > =SUMPRODUCT(N(A1:A111<>""))
> > > > Bob Umlas
> > > > Excel MVP
> > > >
> > > > "Nadine" wrote:
> > > >
> > > > > I have a column that has about 111 rows to it. In each row is a formula that
> > > > > will find an invoice number if it exists elsewhere. The are alpha-numeric
> > > > > invoice #s.
> > > > >
> > > > > I need to count the number of invoices found. If I use the COUNTA formula
> > > > > for that column it will count those cells that have the fomula in it but no
> > > > > result. I only want to count those cells that have a result from the
> > > > > formula. Any ideas for using Excel 2003? Thanks.

--

Dave Peterson
From: Tom Hutchins on
In Excel 2003 you can't use whole columns with SUMPRODUCT. If you change M:M
to M1:M111 does it work?

Hutch

"Nadine" wrote:

> I copied the formula into my worksheet and changed A1:A111 to M:M and the
> result is #NUM. Is this due to the fact that my invoice numbers are
> alpha-numeric? Thanks.
>
> "Bob Umlas, Excel MVP" wrote:
>
> > The formula can't produce #NUM unless the range already has a #NUM in it. Are
> > you sure you entered it as written?
> >
> > "Nadine" wrote:
> >
> > > I get the #NUM result. Thanks for trying.
> > >
> > > "Bob Umlas, Excel MVP" wrote:
> > >
> > > > =SUMPRODUCT(N(A1:A111<>""))
> > > > Bob Umlas
> > > > Excel MVP
> > > >
> > > > "Nadine" wrote:
> > > >
> > > > > I have a column that has about 111 rows to it. In each row is a formula that
> > > > > will find an invoice number if it exists elsewhere. The are alpha-numeric
> > > > > invoice #s.
> > > > >
> > > > > I need to count the number of invoices found. If I use the COUNTA formula
> > > > > for that column it will count those cells that have the fomula in it but no
> > > > > result. I only want to count those cells that have a result from the
> > > > > formula. Any ideas for using Excel 2003? Thanks.
From: Nadine on
Unfortunately not since the data in the column will be expanding and
contracting. I writing the formula in a template to be used for each month.
I have a workaround but was hoping to have it better. Oh, well. Thank you.

"Tom Hutchins" wrote:

> In Excel 2003 you can't use whole columns with SUMPRODUCT. If you change M:M
> to M1:M111 does it work?
>
> Hutch
>
> "Nadine" wrote:
>
> > I copied the formula into my worksheet and changed A1:A111 to M:M and the
> > result is #NUM. Is this due to the fact that my invoice numbers are
> > alpha-numeric? Thanks.
> >
> > "Bob Umlas, Excel MVP" wrote:
> >
> > > The formula can't produce #NUM unless the range already has a #NUM in it. Are
> > > you sure you entered it as written?
> > >
> > > "Nadine" wrote:
> > >
> > > > I get the #NUM result. Thanks for trying.
> > > >
> > > > "Bob Umlas, Excel MVP" wrote:
> > > >
> > > > > =SUMPRODUCT(N(A1:A111<>""))
> > > > > Bob Umlas
> > > > > Excel MVP
> > > > >
> > > > > "Nadine" wrote:
> > > > >
> > > > > > I have a column that has about 111 rows to it. In each row is a formula that
> > > > > > will find an invoice number if it exists elsewhere. The are alpha-numeric
> > > > > > invoice #s.
> > > > > >
> > > > > > I need to count the number of invoices found. If I use the COUNTA formula
> > > > > > for that column it will count those cells that have the fomula in it but no
> > > > > > result. I only want to count those cells that have a result from the
> > > > > > formula. Any ideas for using Excel 2003? Thanks.
From: Nadine on
Is there a way to have this return a Yes or No for example instead of a 1 or
0? Thanks so much.

"Bob Umlas, Excel MVP" wrote:

> =SUMPRODUCT(N(A1:A111<>""))
> Bob Umlas
> Excel MVP
>
> "Nadine" wrote:
>
> > I have a column that has about 111 rows to it. In each row is a formula that
> > will find an invoice number if it exists elsewhere. The are alpha-numeric
> > invoice #s.
> >
> > I need to count the number of invoices found. If I use the COUNTA formula
> > for that column it will count those cells that have the fomula in it but no
> > result. I only want to count those cells that have a result from the
> > formula. Any ideas for using Excel 2003? Thanks.