Sum of data with time being a factor (advanced, So i'm advised)

[pmw19800]

Hello

I’ve tried posting something similar on the formula section of the forum, no solution has been offered and I very much doubt one will be found as I’m told its beyond formula help..

JLR Report Tab – Colum D reports total qty that’s been built for whatever part number appears in Colum A... Raw Data is on MFG tab (can’t be changed) as it’s pasted in

I need the breakdown of what is built on each shift Colum E, F, G
06:00 - 14:30
1400: - 22:30
22:30 - 06:30

the MFG tab is dynamic and changes day to day, the layout is always the same

Please see attached expected outcome

any attempted help is appreciated

[XLent]

If I've understood...

E4:=SUMIFS(MFG!$E:$E,MFG!$A:$A,$A4,MFG!$C:$C,">=06:30",MFG!$C:$C,"<14:30")

F4:=SUMIFS(MFG!$E:$E,MFG!$A:$A,$A4,MFG!$C:$C,">=14:30",MFG!$C:$C,"<22:30")

G4:=D4-SUM(E4:F4)

copied down

I'd advise against using full column ranges for SUMIFS - and suggest you use a Dynamic Named Range instead.

edit: you could also reduce the number of SUMIFS calcs executed with pre-emptive test on D, i.e. =IF($D4=0,0,SUMIFS(...))

[pmw19800]

thats great, works very well