counting nonblank cells
 

I have a student list that is constantly being added to throughout the year
and I need a formula that would count the number of students on the whole
excel sheet without having to scroll down to see how many rows there are. I
have used the count row function but I don't particularly care for it in this
file. I have been playing around with the different count functions but I
have not found one that works. Is there a function that counts non blank
cells?
Any suggestions
Excel 2002
A1:5000
first name

Thanks Suzanne

Assuming the cells are empty or non empty (no formulas, no "blank" spaces)
you can use

=COUNTA(Range)


will count all non blank cells

If you only want to count text

=SUMPRODUCT(--(ISTEXT(Range)))


Regards,

Peo Sjoblom

"sbrimley" wrote:

I have a student list that is constantly being added to throughout the year
and I need a formula that would count the number of students on the whole
excel sheet without having to scroll down to see how many rows there are. I
have used the count row function but I don't particularly care for it in this
file. I have been playing around with the different count functions but I
have not found one that works. Is there a function that counts non blank
cells?
Any suggestions
Excel 2002
A1:5000
first name

Thanks Suzanne

Thank you, it worked but with one small problem, I have more rows than the
number that is being returned from the formula you gave me. I am off by 10.
I even took into consideration my header rows
I used the second formula, just counting text. If there is a space after
the entry would that cause a problem?

"Peo Sjoblom" wrote:

Assuming the cells are empty or non empty (no formulas, no "blank" spaces)
you can use

=COUNTA(Range)


will count all non blank cells

If you only want to count text

=SUMPRODUCT(--(ISTEXT(Range)))


Regards,

Peo Sjoblom

"sbrimley" wrote:

I have a student list that is constantly being added to throughout the year
and I need a formula that would count the number of students on the whole
excel sheet without having to scroll down to see how many rows there are. I
have used the count row function but I don't particularly care for it in this
file. I have been playing around with the different count functions but I
have not found one that works. Is there a function that counts non blank
cells?
Any suggestions
Excel 2002
A1:5000
first name

Thanks Suzanne

Perhaps try:
=SUMPRODUCT((ISTEXT(Range))*(TRIM(Range)<""))
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"sbrimley" wrote in message
...
Thank you, it worked but with one small problem, I have more rows than the
number that is being returned from the formula you gave me. I am off by
10.
I even took into consideration my header rows
I used the second formula, just counting text. If there is a space after
the entry would that cause a problem?

No, if you mean like a name in a cell with a trailing space, that wouldn't
matter; I am sure there is an explanation, how does your formula look like?
You can test this, select the whole range (if the range is A2:A250, type
that in the name box Above column A/Row 1, then press enter), then right
click in the statusbar to the left of the NUM and select count

--
Regards,

Peo Sjoblom


"sbrimley" wrote in message
...
Thank you, it worked but with one small problem, I have more rows than the
number that is being returned from the formula you gave me. I am off by
10.
I even took into consideration my header rows
I used the second formula, just counting text. If there is a space after
the entry would that cause a problem?

"Peo Sjoblom" wrote:

Assuming the cells are empty or non empty (no formulas, no "blank"
spaces)
you can use

=COUNTA(Range)


will count all non blank cells

If you only want to count text

=SUMPRODUCT(--(ISTEXT(Range)))


Regards,

Peo Sjoblom

"sbrimley" wrote:

I have a student list that is constantly being added to throughout the
year
and I need a formula that would count the number of students on the
whole
excel sheet without having to scroll down to see how many rows there
are. I
have used the count row function but I don't particularly care for it
in this
file. I have been playing around with the different count functions
but I
have not found one that works. Is there a function that counts non
blank
cells?
Any suggestions
Excel 2002
A1:5000
first name

Thanks Suzanne

Max,

I can't see that it would return more rows, if there are spaces in what
looks like empty cells it will return less
rows but the OP said the formula returned less rows than what she has. As I
understand it she expected the formula to return a greater value?

--
Regards,

Peo Sjoblom


"Max" wrote in message
...
Perhaps try:
=SUMPRODUCT((ISTEXT(Range))*(TRIM(Range)<""))
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"sbrimley" wrote in message
...
Thank you, it worked but with one small problem, I have more rows than
the
number that is being returned from the formula you gave me. I am off by
10.
I even took into consideration my header rows
I used the second formula, just counting text. If there is a space after
the entry would that cause a problem?

Peo, you're right. Thanks.

Think I was 180% off in the interp of the OP's feedback.
Pl dismiss the suggestion.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Peo Sjoblom" wrote in message
...
Max,

I can't see that it would return more rows, if there are spaces in what
looks like empty cells it will return less
rows but the OP said the formula returned less rows than what she has. As
I
understand it she expected the formula to return a greater value?

--
Regards,

Peo Sjoblom

Oops ..

... was 180% off ...

should read:
... was 180 degrees off ...

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----