Calculating the number of cells containing dates within the last m
 

I am a teacher and use a spreadsheet to keep a record of when students have
completed an assignment.

I need to produce a monthly report detailing how many students completed
each unit within the last month.

Is there a function that can do this?

Thanks

Calculating the number of cells containing dates within the last m
 

Louise,

There is, but to properly help you, describe the data and the cells it is
in.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"louise" wrote in message
...
I am a teacher and use a spreadsheet to keep a record of when students
have
completed an assignment.

I need to produce a monthly report detailing how many students completed
each unit within the last month.

Is there a function that can do this?

Thanks

Calculating the number of cells containing dates within the la
 

The course I teach consists of 5 units. I need to record the date each
student completes each of the 5 units and produce a report each month
detailing how many students have completed each unit within that month.

i.e. Unit 1 10 Unit 2 15

The dates are contained in cells F20:J122 and I need a total for each column.

I hope this makes it clearer!

Thanks


"Bob Phillips" wrote:

Louise,

There is, but to properly help you, describe the data and the cells it is
in.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"louise" wrote in message
...
I am a teacher and use a spreadsheet to keep a record of when students
have
completed an assignment.

I need to produce a monthly report detailing how many students completed
each unit within the last month.

Is there a function that can do this?

Thanks

Calculating the number of cells containing dates within the la
 

Louise,

Assuming that the namesare in column A, put the list of months in A123 down
as Jan, Feb, etc.,, then then add this to F123

=SUMPRODUCT(--(TEXT(F$20:F$122,"mmm")=$A123))

and then copy down and across to J

--

HTH

RP
(remove nothere from the email address if mailing direct)


"louise" wrote in message
...
The course I teach consists of 5 units. I need to record the date each
student completes each of the 5 units and produce a report each month
detailing how many students have completed each unit within that month.

i.e. Unit 1 10 Unit 2 15

The dates are contained in cells F20:J122 and I need a total for each
column.

I hope this makes it clearer!

Thanks


"Bob Phillips" wrote:

Louise,

There is, but to properly help you, describe the data and the cells it
is
in.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"louise" wrote in message
...
I am a teacher and use a spreadsheet to keep a record of when students
have
completed an assignment.

I need to produce a monthly report detailing how many students
completed
each unit within the last month.

Is there a function that can do this?

Thanks

Calculating the number of cells containing dates within the la
 

I read it slightly different to Bob, If you have actual dates (ie 10/10/05
NOT 10.10.05) then for Unit 1 try:

=SUMPRODUCT(--(MONTH(F20:F122)=MONTH(TODAY()))*(F20:F122<""))

to return the current month's total which will update to always, (and only),
show this month's total or if you want a record kept then use:

=SUMPRODUCT((MONTH(F20:F122)=10)*(F20:F122<""))

where the 10 is the month number.

--
HTH

Sandy

Replace@mailinator with @tiscali.co.uk


"louise" wrote in message
...
The course I teach consists of 5 units. I need to record the date each
student completes each of the 5 units and produce a report each month
detailing how many students have completed each unit within that month.

i.e. Unit 1 10 Unit 2 15

The dates are contained in cells F20:J122 and I need a total for each
column.

I hope this makes it clearer!

Thanks


"Bob Phillips" wrote:

Louise,

There is, but to properly help you, describe the data and the cells it is
in.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"louise" wrote in message
...
I am a teacher and use a spreadsheet to keep a record of when students
have
completed an assignment.

I need to produce a monthly report detailing how many students
completed
each unit within the last month.

Is there a function that can do this?

Thanks

Calculating the number of cells containing dates within the la
 

Bob & Sandy

Thank you both very much for you time & help, it's really appreciated!

The second suggestion by Sandy is exactly what I was looking for!

This is going to save me so much time, thank you!

Louise

"Sandy Mann" wrote:

I read it slightly different to Bob, If you have actual dates (ie 10/10/05
NOT 10.10.05) then for Unit 1 try:

=SUMPRODUCT(--(MONTH(F20:F122)=MONTH(TODAY()))*(F20:F122<""))

to return the current month's total which will update to always, (and only),
show this month's total or if you want a record kept then use:

=SUMPRODUCT((MONTH(F20:F122)=10)*(F20:F122<""))

where the 10 is the month number.

--
HTH

Sandy

Replace@mailinator with @tiscali.co.uk


"louise" wrote in message
...
The course I teach consists of 5 units. I need to record the date each
student completes each of the 5 units and produce a report each month
detailing how many students have completed each unit within that month.

i.e. Unit 1 10 Unit 2 15

The dates are contained in cells F20:J122 and I need a total for each
column.

I hope this makes it clearer!

Thanks


"Bob Phillips" wrote:

Louise,

There is, but to properly help you, describe the data and the cells it is
in.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"louise" wrote in message
...
I am a teacher and use a spreadsheet to keep a record of when students
have
completed an assignment.

I need to produce a monthly report detailing how many students
completed
each unit within the last month.

Is there a function that can do this?

Thanks