Counting Down to a Blank Row

[Allen]

I have a list of student names in Column A. They are grouped by class
period. The first row in each class period grouping has "Student Name" in
Column A. The subsequent rows have student names and then, after the last
student's name, there is a blank row. The periods have different number of
student in them. I need to put a formula in column G opposite the first name
in each period that will return the number of names in the period.

Example:
Column A Column G
Student Name
SAM Formula returns 4
GEORGE
SLIM
FRANK

Student Name
GEORGE Formula returns 3
ALLEN
HENRY


Student Name
etc

Any ideas would be welcome! Thanks.

 

[Simon Lloyd]

It's a simple Countif, like this =COUNTIF(A1:A20, "George"), but you ar
probably best of "naming" the ranges so you could use thi
=COUNTIF(Period1, "George")

I have a list of student names in Column A. They are grouped by clas
period. The first row in each class period grouping has "Student Name
i
Column A. The subsequent rows have student names and then, after th
las
student's name, there is a blank row. The periods have different numbe
o
student in them. I need to put a formula in column G opposite the firs
nam
in each period that will return the number of names in the period

Example
Column A Column
Student Nam
SAM Formula returns
GEORG
SLI
FRAN

Student Nam
GEORGE Formula returns
ALLE
HENR


Student Nam
et

Any ideas would be welcome! Thanks

--
Simon Lloy

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com

 

[ADB_Seeker]

Allen,
The =CountA formula will do this. =CountA will count all cells within the
range specified that contain any type of data.

Based on your example, let's say the first name for the first course is in
cell A2 and the last one is in cell A5. Based on that, enter the following
formula into cell G2:
=CountA(A2:A5)

The beginning cell of the count is the cell in which the first name is
entered for that course. The ending cell for the countA will be the last is
entered for that course.

If this response is helpful, click on Yes for "Was this post helpful to you?"

Linda

 

[Allen]

This was somewhat helpful. My problem is i want the formula to determine the
last cell to count...or, more specifically, the first row it comes to with a
blank cell. The classes are different sizes and I want each formula to start
with the row it's in (G2 for the first class) and count down until it finds a
Blank cell in Column A and count the number of students between that first
row and the blank row.

 

[ADB_Seeker]

What Simon wrote below is correct, however it will only count cells with
"George" in them. You may use a wildcard with the CountIf formula to select
all cells that contain text. This formula doesn't count blank cells.
=COUNTIF(A2:A7,"*")

I do not know of a formula that selects the formula range based on blank
cells.

It's a simple Countif, like this =COUNTIF(A1:A20, "George"), but you are
probably best of "naming" the ranges so you could use this
=COUNTIF(Period1, "George")

I have a list of student names in Column A. They are grouped by class
period. The first row in each class period grouping has "Student Name"
in
Column A. The subsequent rows have student names and then, after the
last
student's name, there is a blank row. The periods have different number
of
student in them. I need to put a formula in column G opposite the first
name
in each period that will return the number of names in the period.

Example:
Column A Column G
Student Name
SAM Formula returns 4
GEORGE
SLIM
FRANK

Student Name
GEORGE Formula returns 3
ALLEN
HENRY


Student Name
etc

Any ideas would be welcome! Thanks.

--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=158977

Microsoft Office Help

.

 

[MartinaPa]

Hallo,

ADB_Seeker's right. But if you want to save your working time maybe I'll
help you with VBA code:

Sub Student_Count()

Dim cell As Range
Dim x As Byte
Dim y As Byte

For Each cell In Range("G:G")

If cell.Offset(0, -6).Value = "Student Name" Then

y = 1
Do While cell.Offset(y, -6) <> ""
y = y + 1
x = x + 1
Loop
cell.Value = x
End If
x = 0
Next

End Sub

What Simon wrote below is correct, however it will only count cells with
"George" in them. You may use a wildcard with the CountIf formula to select
all cells that contain text. This formula doesn't count blank cells.
=COUNTIF(A2:A7,"*")

I do not know of a formula that selects the formula range based on blank
cells.

It's a simple Countif, like this =COUNTIF(A1:A20, "George"), but you are
probably best of "naming" the ranges so you could use this
=COUNTIF(Period1, "George")

I have a list of student names in Column A. They are grouped by class
period. The first row in each class period grouping has "Student Name"
in
Column A. The subsequent rows have student names and then, after the
last
student's name, there is a blank row. The periods have different number
of
student in them. I need to put a formula in column G opposite the first
name
in each period that will return the number of names in the period.

Example:
Column A Column G
Student Name
SAM Formula returns 4
GEORGE
SLIM
FRANK

Student Name
GEORGE Formula returns 3
ALLEN
HENRY


Student Name
etc

Any ideas would be welcome! Thanks.

--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=158977

Microsoft Office Help

.

 

[Allen]

Thanks. Unfortunately, this was helpful as it says that what I want can't be
done formula-icly. Thanks also to MartinaPa for the VBA method.

What Simon wrote below is correct, however it will only count cells with
"George" in them. You may use a wildcard with the CountIf formula to select
all cells that contain text. This formula doesn't count blank cells.
=COUNTIF(A2:A7,"*")

I do not know of a formula that selects the formula range based on blank
cells.

It's a simple Countif, like this =COUNTIF(A1:A20, "George"), but you are
probably best of "naming" the ranges so you could use this
=COUNTIF(Period1, "George")

I have a list of student names in Column A. They are grouped by class
period. The first row in each class period grouping has "Student Name"
in
Column A. The subsequent rows have student names and then, after the
last
student's name, there is a blank row. The periods have different number
of
student in them. I need to put a formula in column G opposite the first
name
in each period that will return the number of names in the period.

Example:
Column A Column G
Student Name
SAM Formula returns 4
GEORGE
SLIM
FRANK

Student Name
GEORGE Formula returns 3
ALLEN
HENRY


Student Name
etc

Any ideas would be welcome! Thanks.

--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=158977

Microsoft Office Help

.

 

[Allen]

Thanks. i wanted to avoid a VBA macro on this particular spreadsheet as it
will be used by teachers who may or may not allow the macro to open. I just
wrote the CountA range using the blank line between classes as the last row.
If I have to add students to classes in the future, it should adjust the row
numbers in the formula if I insert rows shouldn't it? That's what I'm
banking on.

Hallo,

ADB_Seeker's right. But if you want to save your working time maybe I'll
help you with VBA code:

Sub Student_Count()

Dim cell As Range
Dim x As Byte
Dim y As Byte

For Each cell In Range("G:G")

If cell.Offset(0, -6).Value = "Student Name" Then

y = 1
Do While cell.Offset(y, -6) <> ""
y = y + 1
x = x + 1
Loop
cell.Value = x
End If
x = 0
Next

End Sub

What Simon wrote below is correct, however it will only count cells with
"George" in them. You may use a wildcard with the CountIf formula to select
all cells that contain text. This formula doesn't count blank cells.
=COUNTIF(A2:A7,"*")

I do not know of a formula that selects the formula range based on blank
cells.

It's a simple Countif, like this =COUNTIF(A1:A20, "George"), but you are
probably best of "naming" the ranges so you could use this
=COUNTIF(Period1, "George")

Allen;575206 Wrote:
I have a list of student names in Column A. They are grouped by class
period. The first row in each class period grouping has "Student Name"
in
Column A. The subsequent rows have student names and then, after the
last
student's name, there is a blank row. The periods have different number
of
student in them. I need to put a formula in column G opposite the first
name
in each period that will return the number of names in the period.

Example:
Column A Column G
Student Name
SAM Formula returns 4
GEORGE
SLIM
FRANK

Student Name
GEORGE Formula returns 3
ALLEN
HENRY


Student Name
etc

Any ideas would be welcome! Thanks.


--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=158977

Microsoft Office Help

.

 

[Simon Lloyd]

If you want it to expand and contract when students are added and
removed you need to use my method where you can name each range but use
a dynamically named range explained here at Debbie's site 'Excel Names
-- Excel Named Ranges' (http://www.contextures.com/xlNames01.html)

Thanks. i wanted to avoid a VBA macro on this particular spreadsheet as
it
will be used by teachers who may or may not allow the macro to open. I
just
wrote the CountA range using the blank line between classes as the last
row.
If I have to add students to classes in the future, it should adjust
the row
numbers in the formula if I insert rows shouldn't it? That's what I'm
banking on.

--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)