Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,670
Default How to count the number of cells not empty?

I get a table from cell M2 to W200, and would like to count the number of
cells not empty? which the cell equals to "". Does anyone have any
suggestions?
Thanks in advance for any suggestions
Eric
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 793
Default How to count the number of cells not empty?

Try the formula
=COUNTA(M2:W200)
in any cell outside the table.

"Eric" wrote:

I get a table from cell M2 to W200, and would like to count the number of
cells not empty? which the cell equals to "". Does anyone have any
suggestions?
Thanks in advance for any suggestions
Eric

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,670
Default How to count the number of cells not empty?

it counts the empty cell too,
Do you have any suggestions on how to fix it?
Thanks in advance for any suggestions
Eric

"Sheeloo" wrote:

Try the formula
=COUNTA(M2:W200)
in any cell outside the table.

"Eric" wrote:

I get a table from cell M2 to W200, and would like to count the number of
cells not empty? which the cell equals to "". Does anyone have any
suggestions?
Thanks in advance for any suggestions
Eric

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,670
Default How to count the number of cells not empty?

="", which is not defined as empty cell in excel, so counta function does not
work for my case, do you have any more suggestions?
Thanks in advance for any suggestions
Eric

"Sheeloo" wrote:

Try the formula
=COUNTA(M2:W200)
in any cell outside the table.

"Eric" wrote:

I get a table from cell M2 to W200, and would like to count the number of
cells not empty? which the cell equals to "". Does anyone have any
suggestions?
Thanks in advance for any suggestions
Eric

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 793
Default How to count the number of cells not empty?

=COUNTA(M2:W200) - COUNTIF(M2:W200,"")
Try

"Eric" wrote:

="", which is not defined as empty cell in excel, so counta function does not
work for my case, do you have any more suggestions?
Thanks in advance for any suggestions
Eric

"Sheeloo" wrote:

Try the formula
=COUNTA(M2:W200)
in any cell outside the table.

"Eric" wrote:

I get a table from cell M2 to W200, and would like to count the number of
cells not empty? which the cell equals to "". Does anyone have any
suggestions?
Thanks in advance for any suggestions
Eric



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default How to count the number of cells not empty?

What type of data is in the range? Is it text, numeric, both?

--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
="", which is not defined as empty cell in excel, so counta function does
not
work for my case, do you have any more suggestions?
Thanks in advance for any suggestions
Eric

"Sheeloo" wrote:

Try the formula
=COUNTA(M2:W200)
in any cell outside the table.

"Eric" wrote:

I get a table from cell M2 to W200, and would like to count the number
of
cells not empty? which the cell equals to "". Does anyone have any
suggestions?
Thanks in advance for any suggestions
Eric



  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default How to count the number of cells not empty?

Ok, I got this from your other post:

There is a table from cell M2 to W200, which
contains names and empty cell.


So, that means the range contains only TEXT.

Try this...

=COUNTIF(M2:W200,"?*")

That will count TEXT ONLY and will *exclude* from the count any cells that
contain formula blanks ="".

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
What type of data is in the range? Is it text, numeric, both?

--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
="", which is not defined as empty cell in excel, so counta function does
not
work for my case, do you have any more suggestions?
Thanks in advance for any suggestions
Eric

"Sheeloo" wrote:

Try the formula
=COUNTA(M2:W200)
in any cell outside the table.

"Eric" wrote:

I get a table from cell M2 to W200, and would like to count the number
of
cells not empty? which the cell equals to "". Does anyone have any
suggestions?
Thanks in advance for any suggestions
Eric





  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,670
Default How to count the number of cells not empty?

Whatif the cell contains number only, such as 1, -1, 0.
Do you have any suggestions?
Thank everyone very much for any suggestions
Eric

"T. Valko" wrote:

Ok, I got this from your other post:

There is a table from cell M2 to W200, which
contains names and empty cell.


So, that means the range contains only TEXT.

Try this...

=COUNTIF(M2:W200,"?*")

That will count TEXT ONLY and will *exclude* from the count any cells that
contain formula blanks ="".

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
What type of data is in the range? Is it text, numeric, both?

--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
="", which is not defined as empty cell in excel, so counta function does
not
work for my case, do you have any more suggestions?
Thanks in advance for any suggestions
Eric

"Sheeloo" wrote:

Try the formula
=COUNTA(M2:W200)
in any cell outside the table.

"Eric" wrote:

I get a table from cell M2 to W200, and would like to count the number
of
cells not empty? which the cell equals to "". Does anyone have any
suggestions?
Thanks in advance for any suggestions
Eric





.

  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,651
Default How to count the number of cells not empty?

On Sun, 18 Apr 2010 07:28:01 -0700, Eric
wrote:

I get a table from cell M2 to W200, and would like to count the number of
cells not empty? which the cell equals to "". Does anyone have any
suggestions?
Thanks in advance for any suggestions
Eric


Two different approaches:

=ROWS(M2:W200)*COLUMNS(M2:W200)-COUNTBLANK(M2:W200)


=SUMPRODUCT(--(LEN(M2:W200)0))


--ron
  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default How to count the number of cells not empty?

Whatif the cell contains number only

For numbers *only* :

=COUNT(M2:W200)

For cells that contain either numbers or text and exclude formula blanks:

=COUNT(M2:W200)+COUNTIF(M2:W200,"?*")

--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
Whatif the cell contains number only, such as 1, -1, 0.
Do you have any suggestions?
Thank everyone very much for any suggestions
Eric

"T. Valko" wrote:

Ok, I got this from your other post:

There is a table from cell M2 to W200, which
contains names and empty cell.


So, that means the range contains only TEXT.

Try this...

=COUNTIF(M2:W200,"?*")

That will count TEXT ONLY and will *exclude* from the count any cells
that
contain formula blanks ="".

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
What type of data is in the range? Is it text, numeric, both?

--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
="", which is not defined as empty cell in excel, so counta function
does
not
work for my case, do you have any more suggestions?
Thanks in advance for any suggestions
Eric

"Sheeloo" wrote:

Try the formula
=COUNTA(M2:W200)
in any cell outside the table.

"Eric" wrote:

I get a table from cell M2 to W200, and would like to count the
number
of
cells not empty? which the cell equals to "". Does anyone have any
suggestions?
Thanks in advance for any suggestions
Eric




.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Count the number of columns on row that are not empty within a ran Mathew Excel Discussion (Misc queries) 3 March 30th 10 07:34 PM
Count empty cells with a specific term FreddieP Excel Discussion (Misc queries) 4 May 20th 08 06:10 PM
Count Only Empty Cells AFTER Cells with Data David Excel Worksheet Functions 2 September 15th 06 06:05 PM
count for empty cells tikchye_oldLearner57 Excel Discussion (Misc queries) 6 May 25th 06 08:41 PM
count non empty cells if other cell has certain value Nico Excel Discussion (Misc queries) 5 May 18th 06 10:40 AM


All times are GMT +1. The time now is 11:27 PM.

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

About Us

"It's about Microsoft Excel"