Scan column for text and Sum using time intervals from another column

E

elderdo

New Member
Joined
Aug 2, 2012
Messages
1
I'm trying to create a dynamic "Class Schedule Planner" using Excel 2007. Column A has non-uniform time intervals ranging from 6:00 AM in A3 to 10:15 PM in A58. Column B2,C2,... to H2 has labels for Monday to Sunday.

For a given Day such as Monday in B10 to B15 I have a class name and number: CIT 101. In B16 to B19 I have the word Study. For B24 to B26 I have ENGT 101. Finally, in B43 to B9 I have the word Study again. For B60 I have a formula to computer the number of hours and minutes of class time for CIT and ENGT - =($A$15-$A$10)+($A$36-$A$24). The times are being pulled from Column A.

I would like to have a better formula for B60 that would scan the column and sum the times from column A for all classes - i.e. for all non-blank cells that do not contain the word Study.

Also, I have a formula in B61, =($A$19-$A$16)+($A$49-$A$43), that sums the times from column A for all the cells with the word Study.

I would like the formula for B61 to scan the column and sum the times from column A for all the non-blank cells containing the word Study.

Here's the data for A2:B61
[TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl64"]Time/Day[/TD]
[TD="class: xl65, width: 64"]Monday[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]6:00 AM[/TD]
[TD="class: xl68"][/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]7:00 AM[/TD]
[TD="class: xl68"][/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]8:00 AM[/TD]
[TD="class: xl68"][/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]8:05 AM[/TD]
[TD="class: xl68"][/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]8:30 AM[/TD]
[TD="class: xl68"][/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]9:00 AM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]9:30 AM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]9:40 AM[/TD]
[TD="class: xl69"] CIT 101[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]10:00 AM[/TD]
[TD="class: xl69"] CIT 101[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]10:30 AM[/TD]
[TD="class: xl69"] CIT 101[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]10:45 AM[/TD]
[TD="class: xl69"] CIT 101[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]11:00 AM[/TD]
[TD="class: xl69"] CIT 101[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]11:05 AM[/TD]
[TD="class: xl69"] CIT 101[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]11:05 AM[/TD]
[TD="class: xl67"] Study[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]11:15 AM[/TD]
[TD="class: xl67"] Study[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]11:30 AM[/TD]
[TD="class: xl67"] Study[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]12:00 PM[/TD]
[TD="class: xl67"] Study[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]12:15 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]12:30 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]12:40 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]12:50 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]1:00 PM[/TD]
[TD="class: xl73"]ENGT 101[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]1:30 PM[/TD]
[TD="class: xl73"]ENGT 101[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]2:00 PM[/TD]
[TD="class: xl73"]ENGT 101[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]2:05 PM[/TD]
[TD="class: xl73"]ENGT 101[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]2:15 PM[/TD]
[TD="class: xl73"]ENGT 101[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]2:20 PM[/TD]
[TD="class: xl73"]ENGT 101[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]2:30 PM[/TD]
[TD="class: xl73"]ENGT 101[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]2:50 PM[/TD]
[TD="class: xl73"]ENGT 101[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]3:00 PM[/TD]
[TD="class: xl73"]ENGT 101[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]3:15 PM[/TD]
[TD="class: xl73"]ENGT 101[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]3:30 PM[/TD]
[TD="class: xl73"]ENGT 101[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]3:35 PM[/TD]
[TD="class: xl73"]ENGT 101[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]3:45 PM[/TD]
[TD="class: xl73"]ENGT 101[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]4:00 PM[/TD]
[TD="class: xl67"][/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]4:20 PM[/TD]
[TD="class: xl67"][/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]4:30 PM[/TD]
[TD="class: xl67"][/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]5:00 PM[/TD]
[TD="class: xl67"][/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]5:15 PM[/TD]
[TD="class: xl67"][/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]5:30 PM[/TD]
[TD="class: xl67"][/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]6:00 PM[/TD]
[TD="class: xl67"] Study[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]6:30 PM[/TD]
[TD="class: xl67"] Study[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]6:45 PM[/TD]
[TD="class: xl67"] Study[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]7:00 PM[/TD]
[TD="class: xl67"] Study[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]7:25 PM[/TD]
[TD="class: xl67"] Study[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]7:30 PM[/TD]
[TD="class: xl67"] Study[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]7:50 PM[/TD]
[TD="class: xl67"] Study[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]8:00 PM[/TD]
[TD="class: xl67"][/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]8:30 PM[/TD]
[TD="class: xl67"][/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]8:45 PM[/TD]
[TD="class: xl67"][/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]8:50 PM[/TD]
[TD="class: xl67"][/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]9:00 PM[/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]9:05 PM[/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]9:30 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]10:00 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]10:15 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl71, width: 64"]Daily Class Time[/TD]
[TD="class: xl72, width: 64, align: right"]4:10[/TD]
[/TR]
[TR]
[TD="class: xl71, width: 64"]Daily Study Time[/TD]
[TD="class: xl70, align: right"]2:45[/TD]
[/TR]
</tbody>[/TABLE]

The 4:10 is for Cell B60 with formula =($A$15-$A$10)+($A$36-$A$24) and
2:45 is for Cell B61 with formula =($A$19-$A$16)+($A$49-$A$43).

It is these cells I want to change with a more dynamic formula so I don't have to change the formula when Study times are changde or when the Class times are changed. I want to do this without having to use VBA code. I don't want to save the file with a xlsm file extension for VBA macros. I want the file to keep the xlsx file extension.

Thank you in advance for any help!
 
Sort by date Sort by votes
Welcome to Forum!

Shouldn't your formula be starting from a row prior? Thus --> =($A$15-$A$9)+($A$36-$A$23) instead of =($A$15-$A$10)+($A$36-$A$24)

You can use SUMIF() to get the total for particular subjects. You will have to add an additional column labelled Duration which calculates the time period for each class. (This column of course can be hidden if desired).

Reproduced below are relevant parts of sheet keeping the same data. (HTML makers does not allow more than 30 rows)
Excel Workbook
ABC
1Time/DayDurationMonday
26:00 AM1:00
37:00 AM1:00
48:00 AM0:05
58:05 AM0:25
68:30 AM0:30
79:00 AM0:30
89:30 AM0:10
99:40 AM0:20CIT 101
1010:00 AM0:30CIT 101
1110:30 AM0:15CIT 101
1210:45 AM0:15CIT 101
1311:00 AM0:05CIT 101
1411:05 AM0:00CIT 101
1511:05 AM0:10Study
1611:15 AM0:15Study
1711:30 AM0:30Study
1812:00 PM0:15Study
1912:15 PM0:15
2012:30 PM0:10
2112:40 PM0:10
2212:50 PM0:10
231:00 PM0:30ENGT 101
241:30 PM0:30ENGT 101
252:00 PM0:05ENGT 101
Sheet4
Excel 2003
Cell Formulas
RangeFormula
B2=IF(A3="",0,A3-A2)
Excel Workbook
ABCD
405:15 PM0:15
415:30 PM0:30
426:00 PM0:30Study
436:30 PM0:15Study
446:45 PM0:15Study
457:00 PM0:25Study
467:25 PM0:05Study
477:30 PM0:20Study
487:50 PM0:10Study
498:00 PM0:30
508:30 PM0:15
518:45 PM0:05
528:50 PM0:10
539:00 PM0:05
549:05 PM0:25
559:30 PM0:30
5610:00 PM0:15
5710:15 PM0:00
58
59Daily Class TimeCIT01:2504:25
60ENGT03:00
61Daily Study TimeStudy03:1003:10
Sheet4
Excel 2003
Cell Formulas
RangeFormula
B40=IF(A41="",0,A41-A40)
C59=SUMIF(C:C,"=" & B59 & "*",B:B)
C61=SUMIF(C:C,"=" & B61 & "*",B:B)
D59=($A$15-$A$9)+($A$36-$A$23)
D61=($A$19-$A$15)+($A$49-$A$42)
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
VBA Separate Day and Time to Another Column if condition meet
2
Replies
10
Views
165
SamKhem
S
C
  • Question Question
TIME LIMITATIONS
Replies
1
Views
226
cooleomter
C
V
  • Question Question
time and date fix
Replies
7
Views
186
vmjan02
V
E
  • Question Question
Can you help me know your working hours and absences here?
Replies
6
Views
130
Joe4
Joe4
V
  • Solved
vlookup not working in some case
Replies
4
Views
93
Peter_SSs
Peter_SSs

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top