Counting results of a formula
in [Functions]
|
Prev: copied from web page to excel sheet and cant remove from sheet
Next: Count, Sum, Index,Match or other formula needed
From: Nadine on 4 May 2010 17:24 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 4 May 2010 17:30 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 4 May 2010 18:02 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 4 May 2010 18:06 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 4 May 2010 20:07
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. |