DSum - criteria in different row than value to sum

[Smangler]

My worksheet is not a dbase, nor a spreadsheet, but rather a weekly calendar
in which I count each individual's hours, yet it has to be in a printable
format.

I'm using 4 columns for each day: B=Start time, C=End time, D=Name, E=total
(Column A just helps with the visual references on the schedule)

(Side note: Column E contains the following formula =24*(IF(B2>C2,0,C2-B2)
in order to return a value I can work with.)

A B C D E
Start End Who Hrs
9H 9:00 10:00 1
Introductions Full Company 0
10H 0
10:30 12:00 1.5
11H Act I-i, ii, iii Neil, Patty, 0
Act II-i James 0
12H


I need to calculate the hours for each individual in a day. For instance,
James=2.5 hours, but Carol, who is not called for the 2nd session, would be 1.

Criteria was as follows:
Start Who Hrs
9:00 >0
9:30
10:00
10:30
and so on
=James

Basically what I want to ask is: if B:B is a time (not text) AND D
contains "NAME" OR "Full Company", SUM E:E when B:B is a time.

SUMPRODUCT and SUMIF don't seem to work as my criteria are on different rows.

I appreciate any help someone might be able to give me on the subject.

 

[FloMM2]

Smangler,
This is what I came up with:
Cell A1 = "9H", B1= "Start", C1= "End", D1 = "Who", E1= "Hrs"
Cells B2,C2,B4 and C4 all formatted to "Time 13:00"
Cells E2:E7 formatted as number with 2 decimal places.
Formula in Cell E2 is "=(IF(B2>C2,0,(C2-B2)*24))"
Formula in Cell E5 is "=(IF(B5>C5,0,(C5-B5)*24))"
Formula in Cell E6 is "=(IF(B6>C6,0,(C6-B6)*24))"
Formula in Cell E7 is "=(IF(B7>C7,0,(C7-B7)*24))"

Cell H1 = "Employees", I1 = "Total Hours"
Cells H2 to H7 are "Carol", "James", "Neil", "Patty", "Full Company"
Formula in I2 is "=IF(I6>0,SUM(I6,SUMIF(D2:E7,H2,E2:E7)"
Formula in I3 is "=IF(I6>0,SUM(I6,SUMIF(D2:E7,H3,E2:E7)"
Formula in I4 is "=IF(I6>0,SUM(I6,SUMIF(D2:E7,H4,E2:E7)"
Formula in I5 is "=IF(I6>0,SUM(I6,SUMIF(D2:E7,H5,E2:E7)"
Formula in I6 is "=IF(I6>0,SUM(I6,SUMIF(D2:E7,H6,E2:E7)"

9H Start End Who Hrs
9:00 10:00 Full Company 1.00
10H Introduction 10:30 12:00
11H Act I-I,ii,iii Neil 1.50
Patty 1.50 Act II-i James 1.50
Employees Total Hours
Carol 1.00
James 2.50
Neil 2.50
Patty 2.50
Full Company 1.00

The above is what it looks like.
hth

 

[Smangler]

FloMM2,

Wow, you deserve a medal just for deciphering the gobbly-gook that was my
message!

B1 through E1 are correct. A2=â€9Hâ€, A4=â€10Hâ€, A6=â€11H†and so on until
A30=â€23Hâ€. (Essentially one row per half hour, visually.) B2:B31 and C2:C31
are formatted to “Time 13:00â€. E2:E31 "=(IF(B#>C#,0,(C#-B#)*24))".

So with my example it would be:

B2=â€9:00â€, C2=â€10:00â€, D2 BLANK, E2 formula=â€1.0â€
B3=â€Introductionsâ€, C3 BLANK, D3=â€Full Companyâ€, E3 formula=0
Row 4 is blank (except for the formula in E4=0)
B5=â€10:30â€, C5=â€12:00â€, D5 is BLANK and E5 formula =â€1.5â€
B6=â€Act I-1, 2, 3â€, C6 is BLANK, D6=â€Neil, Patty†and E6 formula =â€0â€
B7=â€Act II-1â€, C6 is BLANK, D7=â€Jamesâ€, and E7 formula=â€0â€

What I want to find out is if D6 and D7 contain “James†AND if D3 contains
either “James†or “Full Company†AND B:B contain a time entry (not text) then
add the corresponding E:E values.

The problem with SumIf is that, when it finds “James†in D7, it returns the
value in E7 and not the one in E5. So my totals using your formulae in I:I
kept returning “1â€. (And I just entered “1†in I6 because the formula there
resulted in a circular reference.)