SUMIF by Name, By Shift Time

[sansai]

Hello,

Sooo this is what I need:

A formula that will SUM the quantity finished by person, during their shift. Currently I have to manually highlight cells to SUM for night shift personnel, automation is necessary for passing this on to others.

SUMIFS by day shift personnel is easy, but I need this for night shift. They start on one day at 6pm, then stop at 6am the next day. The complication comes in the fact that night shift personnel have shifts that carry over into the next day, and then start again later that next day. My knowledge of excel has hit a massive roadblock.


My columns for data look something like attached, which is just a snippet of how I am tracking for the month so far. It starts with Time-in/Time-out, then Processed By, and then Quantity.

To the right is where the day to day data will be summed by day, for Day and Night shift. Night shift, despite spilling over to the following day will need to be counted as the day they started.

After that is where I stack by Name, going down the month to show their daily totals by individual.


This is not the most elegant presentation of data, but it was something I had that functioned for my own reporting purposes. Now others wish to see the madness within and I would greatly appreciate the help to automate!

Thanks!

[protonLeah]

named ranges:
Processor: =Sheet1!$C$2:$C$51
QuantityFinished:=Sheet1!$D$2:$D$51
TimeOut:=Sheet1!$B$2:$B$51

M2:T32:

Please Login or Register  to view this content.

[sansai]

Hey! Thanks for the reply.

To make sure that I understand: the formula for M2:T32 goes into each cell of that range?

The named ranges, you have range fills with specific names. They seem to match the names in in the M2:T32 range. Do I replace the names in the formula with the ranges?

EDIT:
I ask because when I just put the formula in the cell without altering anything, I get #NAME error.

[sansai]

I am also sorry, I should specify more than just the time range. I see I need to list the night shift names.

Carson, Alex
Yung, Robert
Haus, Robin
Rodrigez, Manuel

[protonLeah]

1. The formula is in each cell of M2:T32. Enter it in M2, drag it down and then drag it across.
2. There are several ways to create named ranges. In this example, I selected the names in column C (C2:C51) and entered Processor in the Name box. Same for QuantityFinished (col D) and TimeOut (col B).

*3. I don't know what you want to do with the night shift names, though...

[protonLeah]

For Day (J2)

Please Login or Register  to view this content.

Night (K2)

Please Login or Register  to view this content.

[sansai]

Thanks for the help! You are a real lifesaver!

[sansai]

Actually, when looking at the numbers, it did not quite all add up.

Looking at Alex Carson, for example, on January 3rd the chart only counted 4 done. Counting by hand he did 8 leading up to midnight, and then 4 more after midnight. So the number for January 3rd for Alex should read 12, but it reads 4.

It seems all night shift have this. Just before the midnight swap, the moves are counted for the next day.

[protonLeah]

I'll try to look at it today, not sure if I have time.

[sansai]

I'll try to look at it today, not sure if I have time.

Not a problem! I already tossed the rep your way.

Someone else was able to give me this formula:

=SUMIFS(Data!$G$2:$G$578,Data!$B$2:$B$578,">"&$B2+TIME(6,15,0),Data!$B$2:$B$578,"<="&$B2+TIME(18,15,0))
=SUMIFS(Data!$G$2:$G$578,Data!$B$2:$B$578,">"&$B2+TIME(18,15,0),Data!$B$2:$B$578,"<="&$B2+1+TIME(6,15,0))


Thanks again for the help you gave!