Counting results of a formula
 

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.

Counting results of a formula
 

=COUNTIF(G1:G111,"""")


Gord Dibben MS Excel MVP

On Tue, 4 May 2010 10:10:01 -0700, 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.

Counting results of a formula
 

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

Counting results of a formula
 

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.

Counting results of a formula
 

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.

Counting results of a formula
 

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.

Counting results of a formula
 

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

Counting results of a formula
 

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.

Counting results of a formula
 

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.

Counting results of a formula
 

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.

I think you can just use COUNT function, it will not recongize the # errors and empty cells.

all the best