If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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. |
#9
|
|||
|
|||
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. |
#10
|
|||
|
|||
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. |
Thread Tools | |
Display Modes | |
|
|