Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting empty cell
I have a range of cell e.g. (C3:C20). I need to count #
of cells that users have entered any data. Is the following worksheet function the correct one ? =@countif(C3:C20),"") If each cell from C3:C20 is blank, I expect it returns 0. If I enter data in C5, C7 & C9, I expect it returns 3. If not, what function should I use, please advise. Thanks again for any help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting empty cell
try
=COUNTIF(C3:C20;"0") if entry data is numeric or another way by chip person www.cpearson.com array formula (ctl+shift+enter) =sum(if(C3:C20<"";1;0)) any type of data (String ,Number) "Wellie" wrote in message ... I have a range of cell e.g. (C3:C20). I need to count # of cells that users have entered any data. Is the following worksheet function the correct one ? =@countif(C3:C20),"") If each cell from C3:C20 is blank, I expect it returns 0. If I enter data in C5, C7 & C9, I expect it returns 3. If not, what function should I use, please advise. Thanks again for any help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting empty cell
If just numbers in the cells, then perhaps
=COUNT(C3:C20) Counts the number of cells that contain numbers and also numbers within the list of arguments. Use COUNT to get the number of entries in a number field that's in a range or array of numbers. Syntax COUNT(value1,value2,...) Value1, value2, ... are 1 to 30 arguments that can contain or refer to a variety of different types of data, but only numbers are counted. ----------------------------------------------------------------------- If you need to count numbers / text / logicals then =COUNTA(C3:C20) Counts the number of cells that are not empty and the values within the list of arguments. Use COUNTA to count the number of cells that contain data in a range or array. Syntax COUNTA(value1,value2,...) Value1, value2, ... are 1 to 30 arguments representing the values you want to count. In this case, a value is any type of information, including empty text ("") but not including empty cells. If an argument is an array or reference, empty cells within the array or reference are ignored. If you do not need to count logical values, text, or error values, use the COUNT function. The @ sign is a holdover from ex Lotus users, and has no real meaning in Excel formulas. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Wellie" wrote in message ... I have a range of cell e.g. (C3:C20). I need to count # of cells that users have entered any data. Is the following worksheet function the correct one ? =@countif(C3:C20),"") If each cell from C3:C20 is blank, I expect it returns 0. If I enter data in C5, C7 & C9, I expect it returns 3. If not, what function should I use, please advise. Thanks again for any help. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.600 / Virus Database: 381 - Release Date: 28/02/2004 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting empty cell
Wellie,
Try this formula =SUMPRODUCT(--ROWS((C3:C20)))-SUMPRODUCT((--(ISBLANK((C3:C20))))) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Wellie" wrote in message ... I have a range of cell e.g. (C3:C20). I need to count # of cells that users have entered any data. Is the following worksheet function the correct one ? =@countif(C3:C20),"") If each cell from C3:C20 is blank, I expect it returns 0. If I enter data in C5, C7 & C9, I expect it returns 3. If not, what function should I use, please advise. Thanks again for any help. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting empty cell
Oops, far too complex, try
=SUMPRODUCT(--(C3:C20<"")) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Bob Phillips" wrote in message ... Wellie, Try this formula =SUMPRODUCT(--ROWS((C3:C20)))-SUMPRODUCT((--(ISBLANK((C3:C20))))) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Wellie" wrote in message ... I have a range of cell e.g. (C3:C20). I need to count # of cells that users have entered any data. Is the following worksheet function the correct one ? =@countif(C3:C20),"") If each cell from C3:C20 is blank, I expect it returns 0. If I enter data in C5, C7 & C9, I expect it returns 3. If not, what function should I use, please advise. Thanks again for any help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting occurance of empty cells | Excel Discussion (Misc queries) | |||
Counting Empty Dates within a Range? | Excel Worksheet Functions | |||
Counting number of days up to an empty cell then start over again | Excel Discussion (Misc queries) | |||
PRODUCT counting empty cells as 1 | Excel Worksheet Functions | |||
counting rows to an empty cell and making a chart | Excel Programming |