A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Counting results of a formula



 
 
Thread Tools Display Modes
  #1  
Old May 4th, 2010, 06:10 PM posted to microsoft.public.excel.worksheet.functions
nadine
external usenet poster
 
Posts: 303
Default 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  
Old May 4th, 2010, 06:27 PM posted to microsoft.public.excel.worksheet.functions
Gord Dibben
external usenet poster
 
Posts: 20,252
Default 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  
Old May 4th, 2010, 06:43 PM posted to microsoft.public.excel.worksheet.functions
Bob Umlas, Excel MVP
external usenet poster
 
Posts: 275
Default 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  
Old May 4th, 2010, 07:26 PM posted to microsoft.public.excel.worksheet.functions
nadine
external usenet poster
 
Posts: 303
Default 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  
Old May 4th, 2010, 08:41 PM posted to microsoft.public.excel.worksheet.functions
Bob Umlas, Excel MVP
external usenet poster
 
Posts: 275
Default 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  
Old May 4th, 2010, 10:24 PM posted to microsoft.public.excel.worksheet.functions
nadine
external usenet poster
 
Posts: 303
Default 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  
Old May 4th, 2010, 10:30 PM posted to microsoft.public.excel.worksheet.functions
Dave Peterson
external usenet poster
 
Posts: 19,791
Default 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  
Old May 4th, 2010, 11:02 PM posted to microsoft.public.excel.worksheet.functions
Tom Hutchins
external usenet poster
 
Posts: 722
Default 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  
Old May 4th, 2010, 11:06 PM posted to microsoft.public.excel.worksheet.functions
nadine
external usenet poster
 
Posts: 303
Default 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  
Old May 5th, 2010, 01:07 AM posted to microsoft.public.excel.worksheet.functions
nadine
external usenet poster
 
Posts: 303
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:29 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.