SumIF calculations

  • Thread starter Thread starter NEHicks
  • Start date Start date
N

NEHicks

unit shift name start end
total hours
5D N Reyes, Victoria 0:00 8:00 8:00
5D D McKan, Pinky 7:30 16:00 8:00
3A E McKan, Pinky 15:30 24:00:00 8:00
4D N Wright, June 0:00 8:00 8:00
4D D Garcia,Jose 7:30 16:00 8:00

I am trying to get a summary of the total hours worked on a unit. I have
tried using =SUMIF(A1:A5,"5D", (F1:F5), which gives me a result of 1.0. I
have also tried using the CountA function but that doesn't return a correct
count either.

Any ideas would be appreciated.
 
I'm sure there is probably another way, however:

=SUMIF(A1:A5,"5D",(F1:F5))/0.0416666666666667

HTH,
Paul
 
Times are stored in Excel as fractions of a 24-hour day, so try it
this way:

=SUMIF(A1:A5,"5D",F1:F5)*24

to get the result in hours - format the cell as General or Number.

Hope this helps.

Pete
 
Actually, looking at your sample data more thoroughly, you seem to
have some strange results in your total hours column. 7:30 to 16:00
should give you 8:30 (rows 2 and 5), and you would normally have a
display of 0:00 on row 3 (not 24:00:00), and the difference on this
row is not 8:00 either. What formula do you have in column F ?

Pete
 
Back
Top