| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#11
|
|||
|
|||
Quote:
I think you can just use COUNT function, it will not recongize the # errors and empty cells. all the best
__________________
Thanks Bala |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Counting Results of Formulas | Excel Worksheet Functions | |||
| Counting results from a lookup | Excel Worksheet Functions | |||
| Counting Poll Results | Excel Worksheet Functions | |||
| counting cells in two columns that have the same results | Excel Worksheet Functions | |||
| counting results of a list | Excel Worksheet Functions | |||
Linear Mode
