Sum, sumif, or sumproduct?

[seanrigby]

This is somewhat like a past problem I had, but since it didn't work as I wanted it to, I need help again.

Need help projecting how much labor costs per month for the year. Employees start and stop at different times. Anything over 40 hours a week gets ovetime.

Please see attached for an example.

[JBeaucaire]

Maybe this, entered into the UNMERGED cell B3 as an array formula:

=SUMPRODUCT(--($C$8:$C$11=$A3),--($D$8:$D$11<=B$2),--($E$8:$E$11>=C$2),(VLOOKUP($B$8:$B$11, $C$16:$F$20, MATCH(B$2, $C$14:$G$14, 1), 0)*$H$8:$H$11))

..confirmed with CTRL-SHIFT-ENTER to activate the array. The result should be 800.

Then highlight B3:C3 and do a Format > Cells > Alignment > Horizontal > Center across selection.

Then highlight B3:C3 and copy down. Then B3:C5 and copy across.

[seanrigby]

If I put anything over 40 hours into it, it still comes back as straight time hours. The $H$8:$H$11 somehow has to dictate if it is 40 hours or less. Any idea?

Other than that, it's worked great.

Thanks for your help!

[JBeaucaire]

Phew...try this horrid ugly monster in B3:

EDIT...removed

[JBeaucaire]

No, I forgot to also include the # of weeks...try this one:

=SUMPRODUCT(--($C$8:$C$11=$A3), --($D$8:$D$11<=B$2), --($E$8:$E$11>=C$2), --(IF($H$8:$H$11<=40, (VLOOKUP($B$8:$B$11, $C$16:$F$20, MATCH(B$2, $C$14:$G$14, 1), 0) * $H$8:$H$11), (VLOOKUP($B$8:$B$11, $C$16:$F$20, MATCH(B$2, $C$14:$G$14, 1), 0) * 40) + VLOOKUP($B$8:$B$11, $C$16:$F$20, MATCH(B$2, $C$14:$G$14, 1) + 1, 0) * (($H$8:$H$11)-40)))) * ((C2-B2+1) / 7)

...confirmed as an array.

[seanrigby]

Wow, what a formula! Only problem with it is if a worker starts in the middle of the month, the formula doesn't recognize the worker in that particular month. So I changed the date portion of the formula to

--($D$8:$D$11<=C$2), --($E$8:$E$11>=B$2

Thanks a bunch for your help. I am going to leave it open for the time being, as I haven't tried the other instructions yet...

Then highlight B3:C3 and do a Format > Cells > Alignment > Horizontal > Center across selection.

Then highlight B3:C3 and copy down. Then B3:C5 and copy across.

[seanrigby]

I spoke too soon, as if I start the worker at the beginning of the month and then at the middle of the month, the same result appears. It doesn't calculate how many weeks in that month he/she has worked.

[JBeaucaire]

You need to provide something to look at, or point to specifics on the already uploaded sheet. Thanks.

[seanrigby]

See attached. I copied the same spreadsheet 5 times, using your formula above. 1st tab looks accurate.

2nd tab, I changed the start date in cell D11 to the middle of the month. The formula didn't take into account the worker the entire month of Janary.

3rd tab, I changed the start date in cell D11 to another month. Same result came back in Cell B3 as in the 2nd tab.

So the formula only takes into account any worker who started before the actual month start date.


So I changed the formula in B3 to --($D$8:$D$11<=C$2), --($E$8:$E$11>=B$2), which you will see if the 4th tab.

It does take into account for the worker starting sometime in that month, but does not take into account only working only part of the month.

So it basically has to figure out either how many weeks, or days, the worker works, then do the calculation.

Hope this makes sense.

If it's easier to add one more column and separate the regular hours and OT hours apart, I can do that. But I still need help on figuring out the hours per month please.

[JBeaucaire]

It was just too much try and cram into one cell. With this much going one...pay rate changes, variations on weeks, project references, you just need to break it out into manageable sections.

I let each employee's row become its own little calendar. In March, the pay rates simply switch to the new pay calendar.

Excel's LIST feature is on in the two lower tables. Simply enter a name at the bottom in the offered expansion row and the list will reassert to include the new row permanently.