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

elderdo · Aug 2, 2012

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

Time/Day	Monday
6:00 AM
7:00 AM
8:00 AM
8:05 AM
8:30 AM
9:00 AM
9:30 AM
9:40 AM	CIT 101
10:00 AM	CIT 101
10:30 AM	CIT 101
10:45 AM	CIT 101
11:00 AM	CIT 101
11:05 AM	CIT 101
11:05 AM	Study
11:15 AM	Study
11:30 AM	Study
12:00 PM	Study
12:15 PM
12:30 PM
12:40 PM
12:50 PM
1:00 PM	ENGT 101
1:30 PM	ENGT 101
2:00 PM	ENGT 101
2:05 PM	ENGT 101
2:15 PM	ENGT 101
2:20 PM	ENGT 101
2:30 PM	ENGT 101
2:50 PM	ENGT 101
3:00 PM	ENGT 101
3:15 PM	ENGT 101
3:30 PM	ENGT 101
3:35 PM	ENGT 101
3:45 PM	ENGT 101
4:00 PM
4:20 PM
4:30 PM
5:00 PM
5:15 PM
5:30 PM
6:00 PM	Study
6:30 PM	Study
6:45 PM	Study
7:00 PM	Study
7:25 PM	Study
7:30 PM	Study
7:50 PM	Study
8:00 PM
8:30 PM
8:45 PM
8:50 PM
9:00 PM
9:05 PM
9:30 PM
10:00 PM
10:15 PM

Daily Class Time	4:10
Daily Study Time	2:45

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!

drsarao · Aug 3, 2012

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

A

B

C

1

Time/Day

Duration

Monday

2

6:00 AM