Home |
Search |
Today's Posts |
#1
|
|||
|
|||
CountIF cells are not empty
How can I get the CountIf function to count the number of
cells in a range where the cells are not empty? Typ cell contents... 1a 1b 2.1 2.2 (empty) (empty) 3.2.2 3.2.2 ----- total cell count of non empty cells = 6 total number of cells in range = 8 regards Wayne |
#2
|
|||
|
|||
Hi Wayne
the COUNTA function will count the number of non blank cells in a range =COUNTA(A1:A8) will return 6 with your example data to count the number of blanks use =COUNTBLANK(A1:A8) will return 2 with your example data If, however, you really want to use a COUNTIF function then this will work: =COUNTIF(A1:A8,"<"&"") to count non-blanks Hope this helps Cheers JulieD "Wayne" wrote in message ... How can I get the CountIf function to count the number of cells in a range where the cells are not empty? Typ cell contents... 1a 1b 2.1 2.2 (empty) (empty) 3.2.2 3.2.2 ----- total cell count of non empty cells = 6 total number of cells in range = 8 regards Wayne |
#3
|
|||
|
|||
Assume the source range is A1:A20
Try in say B1: =SUMPRODUCT(--(A1:A20<"")) Adapt the range "A1:A20" to suit Note that you can't use entire column references, e.g. A:A, B:B, etc in SUMPRODUCT but you can use a range like: A1:A65535 -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Wayne" wrote in message ... How can I get the CountIf function to count the number of cells in a range where the cells are not empty? Typ cell contents... 1a 1b 2.1 2.2 (empty) (empty) 3.2.2 3.2.2 ----- total cell count of non empty cells = 6 total number of cells in range = 8 regards Wayne |
#4
|
|||
|
|||
Max wrote:
Assume the source range is A1:A20 Try in say B1: =SUMPRODUCT(--(A1:A20<"")) How can I get the CountIf function to count the number of cells in a range where the cells are not empty? Typ cell contents... 1a 1b 2.1 2.2 (empty) (empty) 3.2.2 3.2.2 ----- total cell count of non empty cells = 6 total number of cells in range = 8 Although the posted illustration at first glance seems simple, in order to decide on a workable formula one really needs to specify what is meant by "empty" (e.g., blanks, empty strings, ', etc.). COUNTIF, COUNTBLANK and SUMPRODUCT count different things as blank. And the above SUMPRODUCT formula won't work, even on what it counts, if there are error values in the range (other than #N/A--go figure!) Alan Beban |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trendlines to ignore empty cells | Charts and Charting in Excel | |||
Convert data of cells to any type: Number, Date&Time, Text | Excel Discussion (Misc queries) | |||
Sumproduct ... Empty Cells vs Spaces? | Excel Discussion (Misc queries) | |||
Empty Cells, Spaces, Cond Format? | Excel Discussion (Misc queries) | |||
empty cells in ranges | Excel Discussion (Misc queries) |