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

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

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

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