Calculating the number of cells containing dates within the last m

[Guest]

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

 

[Bob Phillips]

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)

 

[Guest]

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]

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)

 

[Sandy Mann]

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
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

 

[Guest]

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