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

--

"NEHicks" wrote in message
...
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.

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

On Dec 17, 4:39*pm, NEHicks wrote:
*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.

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

On Dec 17, 4:39*pm, NEHicks wrote:
*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.