CountIF cells are not empty

[Wayne]

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

 

[JulieD]

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

 

[Max]

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

 

[Alan Beban]

Assume the source range is A1:A20

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