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 <at>yahoo<dot>com
----
"sbrimley" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> Perhaps try:
> =SUMPRODUCT((ISTEXT(Range))*(TRIM(Range)<>""))
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "sbrimley" <[email protected]> wrote in message
> news:[email protected]...
>> 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 <at>yahoo<dot>com
----
"Peo Sjoblom" <[email protected]> wrote in message
news:[email protected]...
> 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 <at>yahoo<dot>com
----