|
| 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
April 18th, 2010, 03:28 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
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
April 18th, 2010, 03:32 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
April 18th, 2010, 04:46 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
April 18th, 2010, 05:06 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
April 18th, 2010, 05:24 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
April 18th, 2010, 05:29 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
April 18th, 2010, 05:36 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
April 19th, 2010, 10:51 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
April 19th, 2010, 12:37 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
April 19th, 2010, 09:46 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 .
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
