Formula for counting number of cells

[Guest]

Dear All,

I have a range of cells, say A1 to A50. I want to write a formula in cell
A51 which will display the number of cells in the range which contain the
data (which are not blank). e.g. if only A1 to A5 cells contain some data and
other cells are blank, then it should show 5 as the result. The data can be
either text or numbers.
Is it possible?

Thank you in advance.

Regards,
Pawan

 

[Biff]

Hi!

Try one of these:

=COUNTA(A1:A50)

If there might be formula blanks in any cells the Counta function WILL count
those. This formula WILL NOT count those:

=SUMPRODUCT(--(LEN(A1:A50)>0))

Biff

 

[Guest]

Hi Pawan,

Counting non-blank cells can be done by using "COUNTA" function.

Please note "A" after COUNT.

Example: =COUNTA(A1:A50)

Another example for you:

="There are "& COUNTA(A1:A50) & " non-empty values and
"&COUNTBLANK(A1:A50)&" blank cells from A1 to A50"

With kind regards.

NAVEEN.