Marking cell; totaling all # between marked cell & next marked cel

[Marley]

Hello,

I have created a workbook, including many worksheets for each month of the
year. The first row of each worksheet indicates the days of the month. The
first column lists all students in our classroom. The purpose of the
workbook is to keep track of attendance. The last column of each worksheet
totals the attendance hours/month for each student.

Problem: I need to somehow mark/highlight a cell (any particular date for
any particular student - dates will vary depending on the student) that would
show their last test date. Then I need to create another column at the end
of each sheet that would total the attendance hours for each student since
their last test. That is, when they take another test, I will need to mark
another cell/date (to indicate their new test date) and the total hours since
their last test will need to reset. The "total hours since their last test
date" will reset everytime they take another test...and begin summing from
the most recent test date each time.

Is their a formula that can accomplish this? If so, will it extend to the
next worksheet/month to count the hours between tests seamlessly?

I would greatly appreciate any help with this issue! Many thanks!!!

 

[Sheeloo]

It can be done but you will have to handle lot of complexity...

Like how many tests in a month? It will be easier if there is at most one...
If you have more than one then which cell you will have the total...?

 

[Jessie]

I was afraid it would be very complex...

There will only be one test per month.

 

[Sheeloo]

Can you send the file to me? Mark the headings where you want the result...

I am assuming that the date will be same for all students..

If you have dates from 1-31 on each sheet from B1:AF1

then if you enter the test date in AG1 (say 6) and enter the following in AG2
=SUM(INDIRECT("R"&ROW()&"C"&($AG$1+1)&":R"&ROW()&"C32",0))

and copy down... to get the sum from the date of the test (including the date)

You will have to add an IF statement to pick from the previous month....

 

[T. Valko]

Let's see if this is what you want:

T = test

...........A...........B..........C
1....Header..Header..Header
2.......10..............................
3.......12..............................
4.......14..........T..........36...
5.......10.............................
6........4...........T..........14...
7........2..............................
8........2..............................
9........2...........T...........6...

If that's what you want enter this formula in C2 and copy down as needed:

=IF(B2="T",SUM(A$2:A2)-SUM(C$1:C1),"")